topic Re: Running total based on time in Desktop

Running total based on time

Anonymous — Fri, 13 Nov 2020 14:28:32 GMT

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.

Re: Running total based on time

mahoneypat — Fri, 13 Nov 2020 16:04:41 GMT

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

Re: Running total based on time

Anonymous — Mon, 16 Nov 2020 08:42:32 GMT

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

Re: Running total based on time

v-zhenbw-msft — Wed, 18 Nov 2020 06:27:29 GMT

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.

Re: Running total based on time

Anonymous — Wed, 18 Nov 2020 15:45:04 GMT

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?

Re: Running total based on time

v-zhenbw-msft — Fri, 20 Nov 2020 06:33:49 GMT

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.