Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
If you have a date column, you can use DATEADD
DATEADD is used with CALCULATE and uses the CALENDAR table
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.
Proud to be a Super User!
Can you share some sample records (YearMonthSort columns & some value columns). Other columns not needed. 🙂
Proud to be a Super User!
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
73 | |
65 |