Running total with reset

[Anonymous]

Hi guys,

After several tries, posting a message here was my last resort. 

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)

The data looks as follows, where I want to calculate the Running total over the [Value] column.  I don't use a separate calendar table.

Date	Time	Value
01/08/2021	00:00	0
01/08/2021	01:00	5
01/08/2021	02:00	2
01/08/2021	03:00	1
….	….	…
….	….	…
01/08/2021	23:00	0
02/08/2021	00:00	0
....	.....	....

I have already tried the logic in this SQLBI video on resetting each day that worked fine. But I really want to reset on a specific time of the day.

Any tips or tricks to realize this?

Thanks!

[Icey · Accepted solution]

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.

[Anonymous]

Thanks @Icey! This was exactly what I was looking for. 
I did not know about the Convert function in DAX!

[parry2k]

@Anonymous first and foremost you should add a date dimension in your model and use that as per the pattern provided. it is a best practice to have a date dimension when working with dates, you can easily add one following my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous if you watch SQLBI video, follow the pattern, add a new column when it is 2 AM otherwise FALSE, and then follow the pattern used in the video. 

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi Parry2K,

Thanks for your reply

I tried that but I am stuck on this step (highlighted in red) of the pattern. DATESBETWEEN is only applicable for dates and not datetime format.

Resetting RT =

VAR RefDate = MAX('Date'[Date])
VAR AllResetDates = FILTER(ALL('Date'[Date]),[Reset] = TRUE())
VAR ResetDatesBeforeNow = FILTER(AllResetDates,'Date'[Date] <= RefDate)
VAR LastReset = MAXX(ResetDatesBeforeNow,'Date'[Date])
VAR DatesToUse = DATESBETWEEN( 'Date'[Date], LastReset,RefDate)
VAR Result = CALCULATE([VALUE amount],DatesToUse)
RETURN

Where [Reset] is a seperate measure with the condition of resetting on a specific time.

Result