Skip to main content
cancel
Showing results for 
Search instead 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

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

 

DateTimeValue
01/08/202100:000
01/08/202101:005
01/08/202102:002
01/08/202103:001
….….
….….
01/08/202123:000
02/08/202100:000
.............

 

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
Icey
Community Support
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 )

Icey_0-1630919699612.png

 

 

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

Icey_1-1630919739461.png

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.

View solution in original post

5 REPLIES 5
Icey
Community Support
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 )

Icey_0-1630919699612.png

 

 

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

Icey_1-1630919739461.png

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!

parry2k
Super User
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

 

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.



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.

parry2k
Super User
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. 

 

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.



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,

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors