Running totals in dates with year filter

[Anonymous]

Hi,

I'm trying to calculate at Running Total over dates and then group by month, quarter and year.

I have this measure showing a running total of sales.

It works just fine on regulary dates, but if I group by year or sort out a year it doesn't work anymore:

Total Sales Amount RT =

VAR MaxDate = MAX ('Calendar'[Date])

VAR Result =

CALCULATE(

[Total Sales Amount],

'Calendar'[Date]<MaxDate

)

RETURN

Result

Here I have filtered out 2022, and I would expect RT to follow and start from 0 but it doesn't. 

What's wrong with my measure?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try this

Total Sales Amount RT =
VAR MaxDate =
    MAX ( 'Calendar'[Date] )
VAR Result =
    CALCULATE (
        [Total Sales Amount],
        FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] < MaxDate )
    )
RETURN
    Result

Best Regards,

Stephen Tao

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

[Anonymous]

Thanks, but this only works if you are after YTD.

It will start over 1. jan.

I need it to be a running total regardless of the year.

[Anonymous]

https://docs.microsoft.com/fr-fr/dax/totalytd-function-dax

[Anonymous]

Hi Krestensb,

If you want it by year use totalytd, it will do it automatically