Forum Discussion
Calculate Sum for Max Date -1
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)
)
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
4 Replies
Whitewater100Solution Sage
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
- Whitewater100
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
- tamerj1
Community Champion
Hi deepblue_m45
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)?
