Forum Discussion
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
Solution 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.
- AnonymousNot applicableOh I'll check that out! What do you mean "Calander as it should be"? I'd like to do things correctly!
- bolfri
Solution Sage
Can you share some sample records (YearMonthSort columns & some value columns). Other columns not needed. 🙂
- Syndicate_Admin
Administrator
If you have a date column, you can use DATEADD
DATEADD is used with CALCULATE and uses the CALENDAR table