Forum Discussion

HB13's avatar
HB13
Icon for Helper I rankHelper I
4 years ago
Solved

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

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 ...
Need Help
Tips and Tricks
  • Adescrit's avatar
    Adescrit
    4 years ago

    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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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