Get Rolling Maximum

[PiyushBQ]

Hi,

I'm trying to get the maximum rolling value in a table, till each date.

Here's the table, with the desired output column:

Date	Product	Value	Desired Output (Highest Value Till Date)
Jan-22	A	20	20
Feb-22	A	24	24
Apr-22	A	28	24
Jun-22	A	14	24
Jul-22	A	20	24
Sep-22	A	30	30
Nov-22	A	29	30
Dec-22	A	32	32
Feb-23	A	18	32
Apr-23	A	17	32
Jun-23	A	16	32

When we add a date filter through a slicer, the dynamic max value should be recalculated like this (select date>= June 2022)
Or when we select another product, the max value should be recalculated according to the filtered view.

Date	Product	Value	Desired Output (Highest Value Till Date)
Jun-22	A	14	14
Jul-22	A	20	20
Sep-22	A	30	30
Nov-22	A	29	30
Dec-22	A	32	32
Feb-23	A	18	32
Apr-23	A	17	32
Jun-23	A	16	32

I've written the following code but it's not recalculating the max value when applying the date filter through a slicer.

Dynamic MAX = CALCULATE(MAX('Table'[Value], KEEPFILTERS('Table'[Date] <= MAX('Table'[Date])), REMOVEFILTERS('Table'[Value]))

Any pointers?

Thank you in advance!

[v-stephen-msft · Accepted solution]

Hi @PiyushBQ ,

Based on your logic described, the desired output in Apr-22 should be 28 instead of 24.

I create the dynamic measure as

Dynamic MAX = CALCULATE(MAX('Table'[Value]),FILTER(ALLSELECTED('Table'),[Product]=MAX('Table'[Product])&&[Date]<=MAX('Table'[Date])))

The result changes when the slicer is filtered.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Alf94]

Hello @PiyushBQ,

I think the following may do the trick:

Highest value = 
    MAXX(
        WINDOW(
            1, ABS,
            0, REL,
            ORDERBY( 'Table'[Date], ASC )
        ),
        CALCULATE( SUM( 'Table'[Value] ) )
    )

Let me know if this is ok.