Running Cumulative based on hours

[Anonymous]

I need your help on a DAX formula to calculate the third column 'CumulativeForTheDay' that's is required to be a running total. The required calculation will be for 24 hours starting at '01:00' AM Till midnight '00:00'.

[Anonymous]

Hi @Anonymous,

You can try to use following measure formula, I write formula to use date part as condition to limit cumulative date range:

Measure =
VAR currDate =
    MAX ( Table[TimeStamp] )
RETURN
    CALCULATE (
        SUM ( Table[FlowAtGivenInterval] ),
        FILTER (
            ALLSELECTED ( Table ),
            OR (
                DATEVALUE ( Table[TimeStamp] ) = DATEVALUE ( currDate ),
                Table[TimeStamp]
                    = DATEVALUE ( currDate ) + 1
            )
                && AND ( Table[TimeStamp] > DATEVALUE ( currDate ), Table[TimeStamp] < currDate )
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hello Xiaoxin,

Thanks for your time. Your code is very close to what I need, it just need to provide a value at '01:00' AM as highlighted in the screenshot which will be the same value from the 'FlowAtGivenInterval' column - 78.66 - in this specific date. You can also notice that no value is available at midnight which I want its value to be accounted and included for the cumulative for this day.

Thanks again for your help.

Emad

RunningTotal =

VAR currDate =

MAX ( Daily_Methane_1[TimeStamp] )

RETURN

CALCULATE (

SUM ( Daily_Methane_1[FlowAtGivenInterval] ),

FILTER (

ALLSELECTED ( Daily_Methane_1 ),

OR (

DATEVALUE ( Daily_Methane_1[TimeStamp] ) = DATEVALUE ( currDate ),

Daily_Methane_1[TimeStamp]

= DATEVALUE ( currDate ) + 1

)

&& AND ( Daily_Methane_1[TimeStamp] > DATEVALUE ( currDate ), Daily_Methane_1[TimeStamp] < currDate )

)

)

[Anonymous]

Hi @Anonymous,

Can you please provide some sample data so that we can test and modify dax formula in power bi side?

Regards,

Xiaoxin Sheng

[Anonymous]

Hello Xiaoxin,

Would you please share your email so I can send you a sample file as it looks like I can't upload it here.

Thanks,

Emad

[Anonymous]

Hello Xiaoxin,

Please check this test report. Thanks for your help.

Emad.

Test Report

[Anonymous]

Hi @Anonymous,

I add a condition to summarize yesterday total on 12am, you can use following measure if it works for your requirement:

RunningTotal 2 = 
VAR currDate =
    MAX ( Daily_Methane_1[TimeStamp] )
RETURN
    IF (
        TIMEVALUE ( currDate ) <> TIME(0,0,0),
        CALCULATE (
            SUM ( Daily_Methane_1[FlowAtGivenInterval] ),
            FILTER (
                ALLSELECTED ( Daily_Methane_1 ),
                AND (
                    Daily_Methane_1[TimeStamp] >= DATEVALUE ( currDate ),
                    Daily_Methane_1[TimeStamp] < currDate
                )
            )
        ),
        CALCULATE (
            SUM ( Daily_Methane_1[FlowAtGivenInterval] ) + 0,
            FILTER (
                ALLSELECTED ( Daily_Methane_1 ),
                DATEVALUE ( Daily_Methane_1[TimeStamp] )
                    = DATEVALUE ( currDate ) - 1
            )
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Many thanks in advance Xiaoxin, I'll test it and let you know.

[themistoklis]

@Anonymous

Creata a new table and add as dimension the timestamp sorted in Ascending order.

Then create a measure using the following formula and add it to the table.

If you want to see the running total for a specific date, add a dare slicer and select the date you want

Cumulative = 
CALCULATE (
    SUM ( Table[FlowAtGivenInterval] ),
    FILTER( ALL (Table ),
    Table[TimeStamp] <= MAX( Table[TimeStamp] ))
)

[Anonymous]

Hello themistoklis,

Thanks for taking the time to reply. The problem with your solution is that is doesn't break on new days. It keeps accumulating on consecutive days.