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.
I have a variety of visuals showing trends over various periods and want to create a measure to place in a card visual that will calculate the max date in the visual which I have, below (h/t to @AlB for the guidance here), as well as max date minus 1 month.
So for instance, I have the below code that will show me 2,160 for May 2022, and I'd like a 2nd measure (hopefully in similar format to the max date if possible), that would show me 2,046 for April. I'd like to use them independently but I'd likely combine them to do a current month minus prior month.
Any guidance or suggestions would be appreciated!
Max Date =
VAR __latestdate =
MAX('Date Hierarchy'[Month Year])
RETURN
CALCULATE(
SUM('DataTable-2020-2022'[Value]),
FILTER('Date Hierarchy','Date Hierarchy'[Month Year] = __latestdate)
)
Solved! Go to Solution.
Hi:
Please see attached file. I added a date table to help it along and did some minor transformations to get the data in order. If your data went thru today, the measures could be slighly different. I hope this helps!
https://drive.google.com/file/d/1Yo8mMLjgKW1grfYdZAUShjmyuWLlyS05/view?usp=sharing
Hi:
Please see attached file. I added a date table to help it along and did some minor transformations to get the data in order. If your data went thru today, the measures could be slighly different. I hope this helps!
https://drive.google.com/file/d/1Yo8mMLjgKW1grfYdZAUShjmyuWLlyS05/view?usp=sharing
Here is the excel if ou want to examine with the data I used.
Description | 2021-05 | 2021-06 | 2021-07 | 2021-08 | 2021-09 | 2021-10 | 2021-11 | 2021-12 | 2022-01 | 2022-02 | 2022-03 | 2022-04 | 2022-05 |
Category 1 | 2247 | 1818 | 1405 | 1194 | 2940 | 2263 | 2320 | 1381 | 1965 | 1818 | 1165 | 1554 | 1815 |
Category 2 | 545 | 329 | 726 | 610 | 426 | 602 | 286 | 755 | 906 | 166 | 277 | 905 | 489 |
Please try
Max Date =
VAR __latestdate =
MAXX (
ALLSELECTED ( 'Date Hierarchy'[Month Year] ),
'Date Hierarchy'[Month Year]
)
RETURN
CALCULATE (
SUM ( 'DataTable-2020-2022'[Value] ),
FILTER ( 'Date Hierarchy', 'Date Hierarchy'[Month Year] = __latestdate )
)
Thanks for the reply! I plugged this in and it works great for the May date. Is there a way to use this to show the month before that (the April equivalent)?
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |