Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
96 | |
87 | |
78 | |
72 | |
69 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |