Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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.
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
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.
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
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.
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
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |