Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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.
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.
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.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
61 | |
60 | |
58 |
User | Count |
---|---|
155 | |
119 | |
103 | |
77 | |
71 |