Calculate cumulative running total per day.

[aaronzheng]

Hi, I have application counts grouped by each hour. I would like to have a running total of the applications each day. In other words I want to sum the applications and have it reset for the next day.

The runningtotal is adding up but I need it to reset on each new day.

This is the dax expression for the runningtotal column :

RunningTotal =

CALCULATE(

    SUM('FLA'[ApplicationCount]),

    FILTER(

        ALL(FLA),

FLA[ApplicationHourEST] <= EARLIER(FLA[ApplicationHourEST]

    )

))

Thanks in advance.

[AntoineTRICHET · Accepted solution]

Hi @aaronzheng 

Maybe you can add a calculated column FLA[Date] which is equal to ApplicationHourEST but is only a date and note a datetime.
And then I think the following measure will answer your need :

RunningTotal =

CALCULATE(

    SUM('FLA'[ApplicationCount]),

    FILTER(

        ALL(FLA),

FLA[ApplicationHourEST] <= EARLIER(FLA[ApplicationHourEST]

    )

), VALUES(FLA[Date])= FLA[Date])

[PaulDBrown · Accepted solution]

try the following code:

RunningTotal =
VAR _Date =
    DATE ( YEAR ( FLA[ApplicationHourEST] ), MONTH ( FLA[ApplicationHourEST] ), DAY ( FLA[ApplicationHourEST] ) )
RETURN
    CALCULATE (
        SUM ( 'FLA'[ApplicationCount] ),
        FILTER (
            ALL ( FLA ),
            FLA[ApplicationHourEST] <= EARLIER ( FLA[ApplicationHourEST] )
                && FLA[ApplicationHourEST] = _Date
        )
    )

[AntoineTRICHET]

Hi @aaronzheng 

Try the following measure :

RunningTotal =

CALCULATE(

    SUM('FLA'[ApplicationCount]),

    FILTER(

        ALL(FLA),

FLA[ApplicationHourEST] <= EARLIER(FLA[ApplicationHourEST]

    )

), VALUES(DATE(YEAR(FLA[ApplicationHourEST]),MONTH(FLA[ApplicationHourEST]),DAY(FLA[ApplicationHourEST]))=DATE(YEAR(FLA[ApplicationHourEST]),MONTH(FLA[ApplicationHourEST]),DAY(FLA[ApplicationHourEST]))

Hoping it will help you

[aaronzheng]

Hi Antoine,

I get this error: The VALUES function expects a column reference expression or a table reference expression for argument '1'.

[AntoineTRICHET · Accepted solution]

Hi @aaronzheng 

Maybe you can add a calculated column FLA[Date] which is equal to ApplicationHourEST but is only a date and note a datetime.
And then I think the following measure will answer your need :

RunningTotal =

CALCULATE(

    SUM('FLA'[ApplicationCount]),

    FILTER(

        ALL(FLA),

FLA[ApplicationHourEST] <= EARLIER(FLA[ApplicationHourEST]

    )

), VALUES(FLA[Date])= FLA[Date])