Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
For some YTD calculations I need to extract the last date in my table. Specifically, I need to extract the month of the last date to be able to compare YTD-totals over the years.
I already tried to extract it the following way: LastMonth = MONTH(LASTDATE('Value'(Date)))
Unfortunately, if I try to compare this in a graph over multiple years, it returns January or 1 for 2021, But 12 or December for the other years (which is logical, given that for the other years the last available date is in December).
I specifically need the value to be January for whatever period of time. The measure therefore should not be filtered. Is this a possibility? An alternative workaround for me would be to create in the table an additional calculated column wich would display the last date next to each value, which would therefore show up for every value, even after filtered. I would like the solution to be a bit more 'elegant' though.
Thanks in advance!
Solved! Go to Solution.
You can use
LastDate = MONTH(LASTNONBLANK(Value[Date], MAX(Value[Date])))
or
LastDate = MONTH(MAXX(ALL(Value[Date]), Value[Date]))
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
You can use
LastDate = MONTH(LASTNONBLANK(Value[Date], MAX(Value[Date])))
or
LastDate = MONTH(MAXX(ALL(Value[Date]), Value[Date]))
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |