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 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!

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.

4 Replies

  • Adescrit's avatar
    Adescrit
    Icon for Impactful Individual rankImpactful Individual
    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.

    • Adescrit's avatar
      Adescrit
      Icon for Impactful Individual rankImpactful Individual
      4 years ago

      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]) )

       

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

      This worked beautifully! Thank you so so much!!

  • 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