Rolling sum in matrix categories with slicer

[Anonymous]

Hi,

I'm quite new to DAX and Power BI and I'm having a struggle getting this rolling sum to work with category in matrix using a slicer in the report. We have a month slicer in the report and this sample is for 202111 selection. In matrix we want for each month to be shown rolling 12 month sum per areaid. I have managed to get the rolling sum to work without category, but it does not work with categories. Is this even possible with DAX? I know I could do this with other reporting tools so I would assume this should be possible with DAX too but I just can't figure it out. Here is the DAX I have now.

ActionsRolling:=
VAR endDate = MAX(Calendar[YYYYMM])
VAR startDate = endDate - 100
VAR RollingSum =
CALCULATE(SUM(Actions[checkins]),FILTER(ALL(Actions), AND(Actions[YYYYMM] > startDate, Actions[YYYYMM] <= endDate)))
RETURN RollingSum

Actions_R12:=
VAR endDate = MAX(TimeSlicer[YYYYMM])
VAR startDate = endDate - 100
VAR MeasureR = Actions[ActionsRolling]

RETURN
IF(MIN(
Calendar[YYYYMM]) <= startDate,
BLANK(),
IF(MIN(Calendar[YYYYMM]) > endDate,
BLANK(),
MeasureR))

With this DAX we get the result like this. These numbers in matrix tables might not be 100% accurate as I quickly put them up in excel but you should get the idea of the problem here.

	202012	202101	202102	202103	202104	202105	202106	202107	202108	202109	202110	202111
Area1	141	158	160	167	175	178	182	187	191	190	180	160
Area2	141	158	160	167	175	178	182	187	191	190	180	160
Area3	141	158	160	167	175	178	182	187	191	190	180	160
total	141	158	160	167	175	178	182	187	191	190	180	160

While the result should look like:

	202012	202101	202102	202103	202104	202105	202106	202107	202108	202109	202110	202111
Area1	11	10	9	10	12	12	13	14	15	15	15	15
Area2	116	135	140	147	153	159	163	168	171	173	163	144
Area3	14	13	11	10	10	7	6	5	5	2	2	1
total	141	158	160	167	175	178	182	187	191	190	180	160

Here are the tables with joins.

Thank you in advance!

- Aku

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

After my long time of research, I can finally figure out a method that can help to achieve the output you wanted to get.

In order to deal with the data loss situation in some date rows, I think the only way is to create a calculated table to fill the rows with no data as blank as the new main table to be used, you can create a table like this:

Actions1 =

var _yyyymm=SUMMARIZE(Actions,Actions[YYYYMM])

var _areaid=SUMMARIZE(Actions,Actions[Areaid])

return

CROSSJOIN(_yyyymm,_areaid)

Then create a calculated column in the new table:

Rank by areaid = RANKX(FILTER(ALL(Actions1),[Areaid]=EARLIER(Actions1[Areaid])),[YYYYMM],,ASC,Skip)

Create a slicer table like this:

Slicer = SUMMARIZE(Actions,Actions[YYYYMM])

Create a measure like this:

ActionsRolling1 =

var _selectedrank=CALCULATE(MAX('Actions1'[Rank by areaid]),FILTER(allselected(Actions1),[YYYYMM]=SELECTEDVALUE(Slicer[YYYYMM])))

var _rolling=CALCULATE(SUM(Actions1[Actions]),FILTER(ALL(Actions1),[Areaid]=MAX(Actions1[Areaid])&&[Rank by areaid]<=MAX('Actions1'[Rank by areaid])&&[Rank by areaid]>=MAX('Actions1'[Rank by areaid])-11))

return

IF(MAX('Actions1'[Rank by areaid])>=12&&MAX('Actions1'[Rank by areaid])<=_selectedrank&&MAX('Actions1'[Rank by areaid])>=_selectedrank-11,_rolling,BLANK())

Then you can go to the report page to place the measures and columns in the new table:

And I think you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Hi,
Thank you for your time. This calculation seems to work as we wanted, though it feels little odd to use calculated table to achieve it.
-Aku

[v-robertq-msft]

Hi, 

According to your description and sample picture, I can now clearly understand your requirement, I think you can try to create a calculated table for the slicer and this measure to achieve your needs:

Create a calculate table like this:

Slicer = SUMMARIZE(Actions,Actions[YYYYMM],Actions[Rank by areaid])

Create a measure:

ActionsRolling1 =

var _rolling=CALCULATE(SUM(Actions[Actions]),FILTER(ALL(Actions),[Areaid]=MAX(Actions[Areaid])&&[Rank by areaid]<=MAX(Actions[Rank by areaid])&&[Rank by areaid]>=MAX('Actions'[Rank by areaid])-11))

var _selectedrank=CALCULATE(MAX('Actions'[Rank by areaid]),FILTER(ALL(Actions),[Areaid]=SELECTEDVALUE(Actions[Areaid])))

return

IF(MAX([Rank by areaid])>=12&&MAX([Rank by areaid])<=_selectedrank&&MAX([Rank by areaid])>=_selectedrank-11,_rolling,BLANK())

And you can create two slicers to place the [areaid] and [YYYYMM] to get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Hi,

I'm sorry but I think I was unclear with my clarification. So this is still what I need to present:

With the additional image I provided I tried to explain how the data needs to be calculated. I added another image where I highlighted with color boxes what should be calculated for each value. I'm not 100% sure if the rolling sum is accurate term for this need but my company uses it for this purpose at least. So the sum needs to be for each shown value from past 12 months, including the selected month in the 12 months. Slicer selection in this sample would of been 202109 and like said in original post there can be empty months but in calculation we don't want to skip them, their value would just be 0 for the sum, in this sample I have presented them as empty values but in our data there is no row for these months. Hopefully this clarifies the need.

- Aku

[v-robertq-msft]

Hi, 

According to your description, you said that in your data there is no row for some months, which may cause some confusion, Would like to post some sample data which is more relevant to your true data model (without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Hi,

The original list is accurate, if you look at it closely Area1 and Area3 are missing some months, for Area2 there is row for every month on this selected period though.

- Aku

[v-robertq-msft]

Hi, 

According to your description and sample pictures, I can clearly understand your requirement, I think you can create two calculated columns to achieve your requirements:

Rank by areaid = RANKX(FILTER(ALL(Actions),[Areaid]=EARLIER(Actions[Areaid])),[YYYYMM],,ASC,Dense)

ActionsRolling =

var _rolling=CALCULATE(SUM(Actions[Actions]),FILTER(ALL(Actions),[Areaid]=EARLIER(Actions[Areaid])&&[Rank by areaid]<=EARLIER(Actions[Rank by areaid])))

return

IF([Rank by areaid]>=12,_rolling,BLANK())

And you can get what you want, like this:

Then you can place this column into your matrix as the value field:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Hi,

That is not exactly what I was looking for. The sum needs to be 12 months rolling from the presented month. The solution also requires already summed rows for the rank to work properly, I could sum them first but would make things simpler if there is solution that would not require it, if that is not possible then we must use the summed rows and live with that. As this does not solve my problem I did not test if this would it work with month slicer selection to show the rolling 12 months. I added another sample here how the data would show if we selected 202109 from slicer.

Thanks for your input though.

- Aku

[Anonymous]

To keep the sample data simple I have already summed it with areaid. If the calculation is not possible with 2 different table, we can get the areadid in actions table if necessary. For all months there is no actions for each areaid.

YYYYMM	Areaid	Actions
201901	Area1	5
201902	Area1	1
201903	Area1	1
201905	Area1	2
201906	Area1	2
201909	Area1	1
201910	Area1	2
202001	Area1	2
202002	Area1	1
202003	Area1	2
202004	Area1	1
202005	Area1	2
202006	Area1	1
202008	Area1	1
202009	Area1	1
202101	Area1	1
202103	Area1	3
202104	Area1	3
202105	Area1	2
202106	Area1	2
202107	Area1	1
202108	Area1	2
202109	Area1	1
201901	Area2	21
201902	Area2	7
201903	Area2	9
201904	Area2	13
201905	Area2	16
201906	Area2	13
201907	Area2	6
201908	Area2	4
201909	Area2	3
201910	Area2	10
201911	Area2	13
201912	Area2	7
202001	Area2	3
202002	Area2	9
202003	Area2	9
202004	Area2	7
202005	Area2	6
202006	Area2	15
202007	Area2	9
202008	Area2	8
202009	Area2	8
202010	Area2	11
202011	Area2	19
202012	Area2	12
202101	Area2	22
202102	Area2	14
202103	Area2	16
202104	Area2	13
202105	Area2	12
202106	Area2	19
202107	Area2	14
202108	Area2	11
202109	Area2	10
202110	Area2	1
201901	Area3	1
201902	Area3	2
201903	Area3	1
201905	Area3	3
201906	Area3	1
201907	Area3	1
201909	Area3	3
202011	Area3	1
202012	Area3	1

Here is one of the rolling selections for a reference:

YYYYMM	Areaid	Actions	Rolling total
202001	Area2	3
202002	Area2	9
202003	Area2	9
202004	Area2	7
202005	Area2	6
202006	Area2	15
202007	Area2	9
202008	Area2	8
202009	Area2	8
202010	Area2	11
202011	Area2	19
202012	Area2	12	116
202101	Area2	22	135
202102	Area2	14	140
202103	Area2	16	147
202104	Area2	13	153
202105	Area2	12	159
202106	Area2	19	163
202107	Area2	14	168
202108	Area2	11	171
202109	Area2	10	173
202110	Area2	1	163
202111	Area2	0	144