Calculate running total and reset it

[Anton0315]

Hello guys,

 I have made a running total that sums up every month, but I also want it to be able to restart the running total from the next month if it reaches a certain value (100), but it doesn't work for me. For example, in January I have 50 and in February I have 50 and it collects them for me, but it's cool that I have 100 from March to start over, that is March, I have again 50 April to collect March.

[v-kongfanf-msft]

Hi @Anton0315 ,

Modify formula like below:

CumulativeSumWithReset =
VAR CumulativeSUM =
    CALCULATE (
        SUM ( data[Private KM] ),
        'Calendar 6'[Date] <= MAX ( 'Calendar 6'[Date] )
    )
RETURN
    IF ( CumulativeSUM >= 3000, 0, CumulativeSUM )

Best Regards,
Adamk Kong

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

[danextian]

Hi @Anton0315

Consider integrating OFFSET into one of your measures. I have attached a sample pbix file to assist you in getting started. In this example, the cumulative count resets whenever there is a blank.

[Anton0315]

Hello,
It is not what I want because i don't have null values. 
Look at this pbix file:

I want after it reaches 3000 (the red line to reset the cumalative sum), as you see it should be reseting in August but it is just summing the rest of the amounts it is not cumalative sum. 

This is the meassure which I'm using 

  Var CumaltiveSUM = CALCULATE(sum(data[Private KM]), 'Calendar 6'[Date] <= max('Calendar 6'[Date]))

             RETURN

              IF(Cumaltivesum>=3000, CALCULATE(SUM(data[Private KM])), CumaltiveSUM),

I think there is a problem in the IF statment i have tried to do it alone but I can't.

Thank you in advance