Rolling sum for all categories present in minimum to selected year

[Anonymous]

Hi,

I am trying to calculate rolling sum. There are few of categories missing in some of the years, my requirement is If i select year 2022 i should get rolling sum of all categories present in 2021 & 2022 in "2022" row in matrix same for 2023 row i should get rolling sum of all categories present in 2021,2022 & 2023 in 2023 row.

but i am only getting rolling sum for categories which are present in 2022 in 2022 row. Actual & Expected outputs, measere formula with data are attached below. Can someone please help me with this.

[Anonymous]

Hi All,

This is extention of my previos question, is there a possibility that Total column/Row total should only totals for A & D categories but not remaining categories.  Thanks in advance.

[mwegener · Accepted solution]

Hi @Anonymous ,

I would always recommend the use of a date table.

Have a look at the attached sample file.

[Anonymous]

Hi Marcus Wegener ,

Thank you very much for the solution and suggestion. Really appreciate it. 

[AlexisOlson]

Yes. Without dimension tables, auto-exist is going to cause all kinds of trouble in this sort of scenario.

[Anonymous]

Hi,

Thank you very much. Appreciate it.

[speedramps]

Try something like this ....

Running Total  =

VAR enddate = MAX(Facts[Year])

RETURN

CALCULATE(

SUM(Facts[Amount]),

ALL(Facts[Year]),

Facts[Year] <= enddate

)

How it works ...

A matrix visual applies a default row and column filters to each cell.
in you case Year and Category.

The VAR  gets the years for the current cell it is calcultating.

You then tell dax to calculate ALL years for each cell.
But that will get all years 2021 to 2023 for each cell.
So you use <= to stop adding when it reached the current cell's year. 

Hope you understand ok.

Remember we are BI community voluntrees so please click the thumbs-up for me taking the effort to help you and then accept the solution if it works.  Thank you !