Running total with reset

Hi Anonymous ,

---

 

I try to calculate a running total measure which reset itself to zero every day at a specific hour of the day (let say: 02:00 AM)

 

---

Since you want to reset the running total at 2:00 AM each day, you can just try this:

1. Create a calculated column.

DateTime =
CONVERT ( [Date] & " " & [Time], DATETIME )

2. Create a measure.

Running Total =
VAR CurrentDateTime_ =
    MAX ( 'Table'[DateTime] )
VAR CurrentDate_ =
    MAX ( 'Table'[Date] )
VAR CurrentTime_ =
    MAX ( 'Table'[Time] )
VAR ResetTime_ =
    TIME ( 2, 0, 0 )
VAR StartDateTime_ =
    IF (
        CurrentTime_ < ResetTime_,
        CONVERT ( ( CurrentDate_ - 1 ) & " " & ResetTime_, DATETIME ),
        CONVERT ( CurrentDate_ & " " & ResetTime_, DATETIME )
    )
VAR Result =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DateTime] >= StartDateTime_
                && 'Table'[DateTime] <= CurrentDateTime_
        )
    )
RETURN
    Result

For more details, please check the attached .pbix file.

Best Regards,

Icey

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