Calculating a cumulative value after a blank

[PYL019]

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

[amitchandak]

Is these breaks like the weekend ?

[PYL019]

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.

[v-xuding-msft · Accepted solution]

Hi @PYL019 ,

Please follow the steps to have a try.

• Create a calculated column firstly.

Count Blank Value =
CALCULATE (
    COUNTBLANK ( 'Table'[Forecast] ),
    FILTER ( 'Table', 'Table'[Date] <= EARLIER ( 'Table'[Date] ) )
)

• Create a measure

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.