Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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) 

jj_env_0-1677779951373.png

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

jj_env_1-1677780020094.png

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

jj_env_2-1677780082561.png

 

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

jj_env_3-1677780303075.png

jj_env_5-1677780377402.png

 

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

jj_env_6-1677780447688.png

 

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 4
Syndicate_Admin
Administrator
Administrator

If you have a date column, you can use DATEADD

DATEADD is used with CALCULATE and uses the CALENDAR table

bolfri
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.