Solved: Running total that resets when equal to or greater... - Page 2

vinserra · ‎02-28-2018

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.

Zubair_Muhammad · ‎02-28-2018

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
    )

Regards
Zubair

Please try my custom visuals

View solution in original post

vinserra · ‎03-04-2018

Hi @Zubair_Muhammad

In the illustration I attempted to note why the reset was taking place. Hopefully this makes the reason for resetting more clear. Thank you for your help.

customer number	product	dateofservice	running total	resetting running total
c1	p1	12/30/2017	0	0	start
c1	p1	1/28/2018	29	29
c1	p1	2/27/2018	59	59
c1	p1	3/17/2018	77	0	reset: due to days between 3/17/2018 & 12/30/2018 is > 60
c1	p1	4/12/2018	103	26
c1	p1	4/25/2018	116	39
c1	p1	5/23/2018	144	0	reset: due to days between 5/23/2018 & 3/17/2018 is > 60
c1	p1	7/7/2018	189	45
c1	p1	8/6/2018	219	0	reset: due to days between 8/6/2017 & 5/23/2018 is > 60