cancel
Showing results for
Did you mean:
Regular Visitor

## Rolling sum for all categories present in minimum to selected year

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.

1 ACCEPTED SOLUTION
Super User

Hi @Nova464 ,

I would always recommend the use of a date table.

Have a look at the attached sample file.

Please mark my post as solution, this will also help others.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."

6 REPLIES 6
Regular Visitor

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.

Super User

Hi @Nova464 ,

I would always recommend the use of a date table.

Have a look at the attached sample file.

Please mark my post as solution, this will also help others.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."

Regular Visitor

Hi Marcus Wegener ,

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

Super User

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

Regular Visitor

Hi,

Thank you very much. Appreciate it.

Solution Sage
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 !

Announcements