Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX Expression to return last year's value

I need to create a measure that returns a value for the same month as the current month, except it's last years value.

 

I have a YearMonthSort column in my table

202302 --> The year 2023, Feb

202101 --> The year 2021, Jan

 

... and so on.

 

So my thought was create a measure that uses Calculate and filters based on the MAX of YearMonthSort (which will return the current month) but subtract 100 from that value (which essentially turns it into last year's value with the same month) 

However, this doesn't work. The returns nothing and the bar chart is blank for that value. 

This is also true if I subtract 1, which should give last months value...

 

However, if I subtract 0 or don't subtract anything all all, I DO get the current months value.

 

Finally, if I just type the number is directly, it also DOES NOT work! 

 

My question is twofold:

a) how can I make a measure that accomplishes the goal?

b) Why am I seeing this behavior? What about the filter params of the Calculate function causes this behavior? I feel like if I can understand this behavior, it will lead to a greater undertanding of how DAX works

 

Thank you!

 

 

 

 

4 Replies

  • bolfri's avatar
    bolfri
    Icon for Solution Sage rankSolution Sage

    It's works like this because somewere on the page you have a filter on you fact data for column yearmonthsort. Thats why this measure "don't see" any other values from that columns. Do you consider using calendar as it should be? 🙂 You can believe me that working with calendar is SO MUCH EASIER for development.

    • Anonymous's avatar
      Anonymous
      Not applicable
      Oh I'll check that out! What do you mean "Calander as it should be"? I'd like to do things correctly!
      • bolfri's avatar
        bolfri
        Icon for Solution Sage rankSolution Sage

        Can you share some sample records (YearMonthSort columns & some value columns). Other columns not needed. 🙂

  • If you have a date column, you can use DATEADD

    DATEADD is used with CALCULATE and uses the CALENDAR table