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 everyone, I have a question around how can I reset the cumulative value to 0 after there's a blank value in a table.
What I got from the cumulative formula is (A), where it continues to count after 2 blank forecast days.
I want to be able to reset the blank forecast days back to 0 and start the cumulative calculation again. Can anyone help here?
Thanks!
Date | Forecast | Cumulative Forecast (A) | Cumulative Forecast (B) |
4-mar | 56 | 56 | 56 |
5-mar | 56 | 112 | 112 |
6-mar | 56 | 168 | 168 |
7-mar | |||
8-mar | |||
9-mar | 56 | 224 | 56 |
10-mar | 91 | 315 | 147 |
11-mar | 46 | 361 | 193 |
12-mar | 91 | 452 | 284 |
Solved! Go to Solution.
Hi @PYL019 ,
Please follow the steps to have a try.
Count Blank Value =
CALCULATE (
COUNTBLANK ( 'Table'[Forecast] ),
FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
)
Measure =
CALCULATE (
SUM ( 'Table'[Forecast] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Count Blank Value] = MAX ( 'Table'[Count Blank Value] )
)
)
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Is these breaks like the weekend ?
Sometimes...
I gave a specific example for a specific product at a specific warehouse. I hope you're able to guide me on how I can build a good report.
Hi @PYL019 ,
Please follow the steps to have a try.
Count Blank Value =
CALCULATE (
COUNTBLANK ( 'Table'[Forecast] ),
FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
)
Measure =
CALCULATE (
SUM ( 'Table'[Forecast] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Table'[Date] )
&& 'Table'[Count Blank Value] = MAX ( 'Table'[Count Blank Value] )
)
)
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
81 | |
76 | |
62 | |
60 | |
48 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |