cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Running total with reset

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!

1 ACCEPTED SOLUTION
Community Support

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.

5 REPLIES 5
Community Support

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
Not applicable

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

Super User

@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

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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@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.

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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi Parry2K,

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