Forum Discussion
updated title: Using 2 slicers, comparing only the max value from two range of dates & when max was
Anonymous
@mikelee1701
Not sure this will work with the YEAR listed in your Excel desired result screenshot, but following the same pattern as the formula in the blog link you posted, you can try to create this for max previous value:MaxPrevious=
CALCULATE (
MAX(table[value]),
ALL ( 'Date' ),
USERELATIONSHIP ( 'Date'[Date], 'Previous Date'[Date] )
)And this for the Max previous date:
Previous Date = CALCULATE([Current Date], ALL('Date'), USERELATIONSHIP( 'Date'[Date], PreviousDate[Date]))Where this is the 'Current' date (you can give it a better name, but basically using the main date selection filter):Current Date =VAR MaxValue = [Current Max]RETURNMAXX(FILTER('Table', 'Table'[Value] = MaxValue), 'Table'[Date])*UPDATE: Note this current date measure is different to what is in the attached report - the attached report is INCORRECT. Please use the above syntax instead.Current Max = MAX('Table'[Value])And YoY is just the difference divided:YoY = DIVIDE([Current Max]-[Previous Max], [Previous Max])The challenge is in formatting the visual, so are you flexible with how it looks? See attached example; below signature.
Hi AllisonKennedy ,
Thank you so much for your time.
The values are fine.
However for some reason I am only getting the last date of the filters:
I have plugged in my data into your pbix and I noticed that that the only diff was that my table's cal was actual calendar dates.
Why would this cause it to only show the last date?
this is my actual data (over two years of data):
Thank you,
Mike
Community ChampionSorry, I created the variable and then chose the wrong value. I will edit my original post now so as not to confuse any future users that find this for reference, but please use this measure for date:
Current Date =
VAR MaxValue = [Current Max]
RETURN
MAXX(FILTER('Table', 'Table'[Value] = MaxValue), 'Table'[Date])
Note the difference here is that we use the MaxValue VARIABLE rather than the [CurrentMax] MEASURE in our return value, and this provides the correct result.
Variables are calculated when they are defined, whilst measures are calculated when they are used. So using the VARIABLE for the comparison in the MAXX(FILTER('Table', 'Table'[Value] = MaxValue), 'Table'[Date]) part of the formula ensures that we are using the already defined value for MaxValue. Using the Measure makes that too dynamic and essentially allows the MAXX to see every row in the table. For each row, the max value of that row is itself.
I think I'm just rambling and not making much sense, but the above measure should work better for you and hopefully you get some understanding why.