cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nova464
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.

 

 

PI.PNGdata.PNG

1 ACCEPTED SOLUTION
mwegener
Super User
Super User

Hi @Nova464 ,

 

I would always recommend the use of a date table.

Have a look at the attached sample file.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

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."
twitter - LinkedIn - YouTube - website


View solution in original post

6 REPLIES 6
Nova464
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.

 

mwegener
Super User
Super User

Hi @Nova464 ,

 

I would always recommend the use of a date table.

Have a look at the attached sample file.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

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."
twitter - LinkedIn - YouTube - website


View solution in original post

Hi Marcus Wegener ,

 

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

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

Hi,

 

Thank you very much. Appreciate it.

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors