Subtracting measure value from each row of a table in Microsoft Power BI

Question

I have created two slicers in Power BI for Month and Year. These take the month and year value and in return generate a date.

Eg. Month - May & Year- 2019 generates 01/05/2019 with the help of the a measure formula :

MyFilterDate = DATE(SELECTEDVALUE(ListoY[Year]),SELECTEDVALUE(ListoM[Month],0),"01")

I want to subtract this date with one that is already in a column in the table.

I'm facing an issue when I try to subtract the measure from the existing column by writing DAX.

SUBVALNEW = DATEDIFF([MyFilterDate],Table[Date],DAY)

But when I try to do so, the result is not right. In fact when I try to check the value of [MyFilterDate] in the Data Model, it is not the same value as one calculated in the measure. (It instead looks like it displays the date in Table[Date])

Is there any way to subtract a Dynamically chosen date from a date in a column?
Any help would be appreciated.

Calculated Columns are not a dynamic calculation. You can't expect them to change over a filter like slicers. They run one time and save result. The only dynamic results are measures. You may need to think in what visualization are you trying to show the result and build a measure thinking on that. — ibarrau
@Nacho I see, is there any other feature in Power BI that allows similar calculation? My requirement is that the user selects a month and year, using which I dynamically generate a Date (E.g 1/05/2019) and calculate the difference between that a Table[Date] column which contains around 10 rows. I further need to compare the DIFF value with alloted categories (greater than 10 etc.) and create columns for those. — Kate

Aldert Aldert · Accepted Answer · 2019-08-05T14:55:41

I understand what your thinking patern is but power bi works a bit different. Dynamic measures cannot be used with calculated columns. When doing so the value of the measure is not set.

The way to go about this is by creating a Date table:

Date = CALENDAR(MIN(CalendarWeek[Date]);MAX(CalendarWeek[Date]))

Do NOT connect it to the model. From here you can make 2 slicers (based on the Date table). One your set to the month and the other to the year.

Next step is to adjust the MyFilterDate

MyFilterDate = DATE(SELECTEDVALUE('Date'[Date].[Year]);SELECTEDVALUE('Date'[Date].[MonthNo]);"01")

Make SUBVALNEW a measure (cannot be a column because it is dynamic!)

SUBVALNEW = DATEDIFF([MyFilterDate];SELECTEDVALUE(CalendarWeek[Date]) ;DAY)

Ass the Measuer to your visual, see end result below)

Subtracting measure value from each row of a table in Microsoft Power BI

1 Answers