Running total based on time

[Anonymous]

I have the following table that I want to use as a running total for an area chart + a matrix visual:

Additionally, for my table the day doesnt end at 00:00, but after 03:00 am (see last line). I've created an additional column to use the "sort by column" function (along the lines of column = if time < 03:00 then X else X).

How do I create a running total based on time? I tried a view solutions from Google results, but none seem to work. Using the quick measure for running total doesnt work either.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

Are the date and item number column from the same table?

Do you mean creating the slicer like this?

If yes, you can try this measure.

RT = 
VAR thistime =
    MIN ( 'Table'[Time] )
RETURN
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER(
            ALLSELECTED ( 'Table' ),
        'Table'[Time] <= thistime
    ))

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Anonymous]

Apologies, I should have been more clear. When creating the mockup file I noticed that the measure does indeed work. However, the measure does not take into account my slicers (date and item number right now), which I failed to mention in my opening post. Is there a way to adjust the measure so that slicers are always taken into account? Or how can I manually include this?

[v-zhenbw-msft]

Hi @Anonymous ,

Do you have a date column? Or just a Time column?

We create a sample and it doesn’t reset on negatives/positives.

Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[Anonymous]

I tried your formula and it seems to be exactly what I was looking for, however, it seems to reset on negatives/positives:

[mahoneypat]

Not sure you mean with your sort order column but this measure expression should give you your running total based on time column.

RT =
VAR thistime =
    MIN ( Table[TimeColumn] )
RETURN
    CALCULATE (
        SUM ( Table[ValueColumn] ),
        ALLSELECTED ( Table[TimeColumn] ),
        Table[TimeColumn] <= thistime
    )

Replace the table and column names with the actual ones in your model.  Use the measure in a visual with the Time column.

Regards,

Pat