How to create a dynamic DAX measure value that changes according to the filter applied on the visual

[HB13]

Hi all!

I have this DAX measure in Power BI;

CALCULATE ( SUM(Table1[amount]) , YEAR(Table2[date]) = 2021 )

This measure is used in a visual that is being filtered by a _DateTable[date], that is joined to a date column in Table1. The visual filter is currently set to 2021, however when I change it to 2022 the values are incorrect as the measure still points to 2021 in Table2...

Is it possible to write this measure in a way that updates the Table2[date] value in the measure to the same value the _DateTable[date] is filtering for...

So something like;

CALCULATE ( SUM(Table1[amount]) , YEAR(Table2[date]) = YEAR(_DateTable[date]) )

I hope I make sense.

All suggestions will be much appreciated, Thank you!

[Adescrit · Accepted solution]

Hi @HB13 

You could try:

Measure = 
VAR __SelectedYear = SELECTEDVALUE(DateTable[Year])
RETURN
CALCULATE ( SUM(Table1[amount]) , YEAR(Table2[date]) = __SelectedYear )

I'm assuming there is a column [Year] in the DateTable for this to work.

[HB13]

This worked beautifully! Thank you so so much!!

[Adescrit]

This may be over-complicating things though. If your Table1 is connected to DateTable via a date column in both, then your formula only needs to be the below. You can then create a YEAR column in your date table and use that to filter Table1.

CALCULATE ( SUM(Table1[amount]) )

[amitchandak]

@HB13 , with help from a date/year table

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

or

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA