Running total that resets when equal to or greater than a set value

[vinserra]

Is there a way using either Power Query M or DAX, to add a column to your data set that calculates a running total which will reset at reach valued and begin again as a running total on the line below the reached value? 

For example (illustration provided below):  I have a list of customers, their products and service dates.  I want to calculate the difference between their first service date to each of their subsequent service dates based on their product.  However, should the amount of days between their first service date and the current service date exceed or equal 60, then that current line now becomes the "first" service date and it's total is 0 and the running total now starts there.  

customer number	product	dateofservice	running total	"resetting running total"
c1	p1	12/30/2017	0	0
c1	p1	1/28/2018	29	29
c1	p1	2/27/2018	59	59
c1	p1	3/17/2018	77	0
c1	p1	4/12/2018	103	26
c1	p1	4/25/2018	116	39

Hopefully that is a clear explanation.  I'm able to do running totals in a variety of ways in Power BI, but unable to do a reset.  I can make this work in Excel but haven't been able to translate the logic into M or DAX.  

Thanks in advance for the help. 

[afzalphatan]

Hi, Below Formula in calcualted colum  should help you...

Reset Total=
Var MinDate = CALCULATE(MIN(Table1[DateOfService]), ALLEXCEPT(Table1,Table1[Customer No], Table1[Product]))
Var RefPatternNo = ROUNDDOWN(DIVIDE(DATEDIFF(MinDate, Table1[DateOfService], DAY), 60), 0)
Var PatternTable = FILTER(CALCULATETABLE(Table1, ALLEXCEPT(Table1, Table1[Customer No], Table1[Product])), 
			ROUNDDOWN(DIVIDE(DATEDIFF(MinDate, Table1[DateOfService], DAY), 60), 0) = RefPatternNo)
Var MinDateInPatternTable = CALCULATE(MIN(Table1[DateOfService]), PatternTable)
Return (Table1[DateOfService] -MinDateInPatternTable)

Below is the result 

[vinserra]

Hi @afzalphatan

Thank you very much for your solution.  Based on the screenshot you provided I would expect the last two values to be 45 and 0 for rows 8 and 9 since the reset would have started again on row 7 and then again on row 9.  Would you agree?

[afzalphatan]

Got it... I am not sure if this can be done with DAX .... But I will give it another try

[afzalphatan]

Reset is only after 60 days ...Orr  factor of 60 days, like 60 , 120, 180 so onn?? Reset after 60 days, 120 days , 180 days??

[Zubair_Muhammad]

Hi @vinserra

This would look  crazy  ..... But I dont know if there is better way

But it works with the sample data

=
VAR Startingdate =
    CALCULATE (
        MIN ( Table1[dateofservice] ),
        ALLEXCEPT ( Table1, Table1[customer number], Table1[product] )
    )
VAR FirstResetDate =
    CALCULATE (
        MIN ( Table1[dateofservice] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer number], Table1[product] ),
            DATEDIFF ( Startingdate, Table1[dateofservice], DAY ) > 60
        )
    )
VAR SecondResetDate =
    CALCULATE (
        MIN ( Table1[dateofservice] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer number], Table1[product] ),
            ( Table1[dateofservice] - FirstResetDate )
                > 60
        )
    )
VAR ThirdResetDate =
    CALCULATE (
        MIN ( Table1[dateofservice] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer number], Table1[product] ),
            ( Table1[dateofservice] - SecondResetDate )
                > 60
        )
    )
VAR RunningTotalatFirstResetDate =
    CALCULATE (
        SUM ( Table1[running total] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer number], Table1[product] ),
            Table1[dateofservice] = FirstResetDate
        )
    )
VAR RunningTotalatSecondResetDate =
    CALCULATE (
        SUM ( Table1[running total] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer number], Table1[product] ),
            Table1[dateofservice] = SecondResetDate
        )
    )
VAR RunningTotalatThirdResetDate =
    CALCULATE (
        SUM ( Table1[running total] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer number], Table1[product] ),
            Table1[dateofservice] = ThirdResetDate
        )
    )
RETURN
    SWITCH (
        TRUE (),
        Table1[dateofservice] < FirstResetDate, Table1[running total],
        Table1[dateofservice] < SecondResetDate, Table1[running total] - RunningTotalatFirstResetDate,
        Table1[dateofservice] < ThirdResetDate, Table1[running total] - RunningTotalatSecondResetDate,
        Table1[running total] - RunningTotalatThirdResetDate
    )

[vinserra]

@Zubair_Muhammad 

This is really great work.  Unfortunately it's limited to three resets when the data could need several.  I have Power BI tied to a live database that upon updates can extend the customer/product service date list that could be over a long period of time with several resets.  In Excel this is easy to do since you can add the cell above to the current cell and check the running total against 60 then reset it to 0 when it's over and continue from there.  I'm not sure if that method is possible with Power BI.  

[Lind25]

Here is a solution using Power Query / M which I think makes this much simpler as Power Query can iterate through each row.

I assume you have already calculated the difference between each row and this result is stored in a column called Difference.

From here the code is:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(AddedIndex, "Running total reset", each List.Accumulate(List.FirstN(AddedIndex[Difference],[Index]),0,(state,current)=>if state+current > 60 then 0 else state+current))
in
    #"Added Custom"

Explanation:

AddedIndex - allows us to know filter the data for all rows up to and including the current row.

List.FirstN(AddedIndex[Difference],[Index]) - filters the data to only include rows up to and including the current row, using the Index column.

List.Accumulate - cycles through the filtered list from above and sums up every row, resetting to zero at 60 each time.

[vinserra]

Hi @Lind25

Thank you very much for your power query solution.  Unfortunately it takes a very long time to calculate and I'm unsure if it is working as I haven't been able to allow the full calculation to run.  I will try to give it some time to run tomorrow, but if the run time is too long I don't think I will be able to use it for future use.  Thank you again.  

[Lind25]

It should run slightly faster if a new step is added to define the list and using list.buffer on this step.
How many rows of data do you have?

[vinserra]

At the moment there are 4900 rows but that will increase daily.  

[afzalphatan]

Can you please show the pic of the result??

[Lind25]

Here you go:

[afzalphatan]

But its not matching the required reesult 

[Lind25]

It matches the result specified in your post: http://community.powerbi.com/t5/Desktop/Running-total-that-resets-when-equal-to-or-greater-than-a-se...

See the below screenshot that now uses your differences.

[Zubair_Muhammad]

@vinserra

[parry2k]

What was the field used to calculate running total?

[vinserra]

It would be the difference between the considered first date and the current row's date of a customer and product.  Alternatively it could be a sum of the difference between the current row date and the row above its date for a customer and product.  

[Zubair_Muhammad · Accepted solution]

HI @vinserra

Try this calculated Column

Resetting Running Total =
VAR Startingdate =
    CALCULATE (
        MIN ( Table1[dateofservice] ),
        ALLEXCEPT ( Table1, Table1[customer number], Table1[product] )
    )
VAR ResetDate =
    CALCULATE (
        MIN ( Table1[dateofservice] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer number], Table1[product] ),
            DATEDIFF ( Startingdate, Table1[dateofservice], DAY ) > 60
        )
    )
VAR RunningTotalatResetDate =
    CALCULATE (
        SUM ( Table1[running total] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[customer number], Table1[product] ),
            Table1[dateofservice] = ResetDate
        )
    )
RETURN
    IF (
        Table1[dateofservice] < ResetDate,
        Table1[running total],
        Table1[running total] - RunningTotalatResetDate
    )

[vinserra]

Hi @Zubair_Muhammad

Sorry to come back to this.  What I've noticed is that if the customer/product hits the first reset date and the running total begins again, the new running total can go over 60 without a reset.  I should have said that the running total must always reset at 60, which could happen more than once.  Is there a way to modify the current code to do so?  Thanks again for your help.  

[Zubair_Muhammad]

Hi @vinserra

Could you illustrate this with data and expected results? (Just like you did at the topic start)

I will try to solve it