topic Rolling Sum issue - help in DAX Commands and Tips

Rolling Sum issue - help

Anonymous — Tue, 27 Sep 2022 13:19:30 GMT

Hi, can someone help me to calculate the Rolling Sum (the last column in the below example). I have Pro Id, Year-month, Total Bonus for each Year-month, and few other columns in the visual as shown below. I need a Rolling Sum of Bonus (last column) for each Pro Id as shown in example below.

Tried the following DAX measure, but it's not giving me expected results. Please advise.

Rolling Sum Bonus = CALCULATE('Mesures Table'[Total Bonus], FILTER(ALLSELECTED(Table1),Table1[Pro Id]<=MAX(Table1[Pro Id])),FILTER(ALL('Date Dim'),'Date Dim'[Year-Month Code] <= MAX('Date Dim'[Year-Month Code]) && 'Date Dim'[Year] <= MAX('Date Dim'[Year])))

Pro Id	Name	Account Name	Profile Name	Year-Month	Total Bonus	Rolling Sum Bonus
2200720		ALAMO	AGH DIST	Sep-21	17707	17707
2200720		ALAMO	AGH DIST	Oct-21	1897.5	19604.5
2200720	ABC Inc	ALAMO	AGH DIST	Feb-22	2307.36	21911.86
2200720	ABC Inc	ALAMO	AGH DIST	Apr-22	1129.3	23041.16
2200720	ABC Inc	ALAMO	AGH DIST	Jun-22	8806.7	31847.86
2200720	ABC Inc	ALAMO	AGH DIST	Jul-22	713	32560.86
2200720	ABC Inc	ALAMO	AGH DIST	Aug-22	607.2	33168.06
2379628	ABC Inc	XYZ	AGI Inc	Oct-21	638.94	638.94
2379628	ABC Inc	XYZ	AGI Inc	Mar-22	1426	2064.94
2379628	ABC Inc	XYZ	AGI Inc	Jun-22	92	2156.94
2379628	ABC Inc	XYZ	AGI Inc	Jul-22	577.3	2734.24

Re: Rolling Sum issue - help

Jihwan_Kim — Tue, 27 Sep 2022 14:15:38 GMT

Hi,

I am not quite sure if I understood your question correctly, but I assume you want to NOT show the rolling total result if the bonus is empty.
Please check the below picture and the attached pbix file.

I tried to create a sample pbix file that the datamodel looks like below.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Rolling sum measure: = IF ( NOT ISBLANK ( [Total bonus measure:] ), CALCULATE ( [Total bonus measure:], 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) )

Re: Rolling Sum issue - help

Anonymous — Tue, 27 Sep 2022 14:39:00 GMT

Hi, Thanks for the code. But, the rolling sum messed up as soon as I added other columns, Account Name and Name. please see below, starting row 3 it started the new rolling sum, as this is part of same Pro Id, the rollowing should continue from Top.

please advise. Appreciate your help.

Re: Rolling Sum issue - help

Jihwan_Kim — Tue, 27 Sep 2022 17:52:23 GMT

Hi,

Please try the below measure that I amended a little bit, and please check the attached file.

Rolling sum measure: = IF ( NOT ISBLANK ( [Total bonus measure:] ) && HASONEVALUE ( Data[Pro Id] ), CALCULATE ( [Total bonus measure:], FILTER ( ALL ( Data ), Data[Pro Id] = MAX ( Data[Pro Id] ) ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) )

Re: Rolling Sum issue - help

Anonymous — Tue, 27 Sep 2022 22:21:22 GMT

Ecellent. It worked. Thank you!

Appreciated.