Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Community,
I hav a table (Case) with :
1. Case ID
2. Casedatevalue which contains date and is of date type
I want to have count of case id by month and also rolling average for 12 months.
Kindly assist with a to how to calculate the 12 month rolling average and plot it on the graph.
For example : If Month is March,2023 it should take last 12 month rolling average.
I have tried with quick measure it's not working.
Your input will be of greata help !
I need to have the rolling 12 month rolling average.I need to use casedatevalue as a slicer and when I select year for ex 2023. It should do the 12 month rolling average from the selected year i.e in this case 2023 and the current month. Example : If I select 2023 from slicer, it should do the rolling average from the current selected year taking into account the current date/month.
Hi,
Share data in a format the can be pasted in an MS Excel file.
I need to use casedatevalue as a slicer and when I select year for ex 2023. It should do the 12 month rolling average from the selected year i.e in this case 2023 and the current month. Example : If I select 2023 from slicer, it should do the rolling average from the current selected year taking into account the current date/month.
CaseId | CaseDateValue |
22263 | 12/24/2022 0:00 |
22329 | 2/13/2023 0:00 |
22359 | 12/5/2022 0:00 |
72568 | 12/31/2022 0:00 |
77068 | 12/21/2022 0:00 |
78229 | 12/6/2022 0:00 |
78845 | 12/7/2022 0:00 |
78865 | 12/12/2022 0:00 |
81065 | 12/8/2022 0:00 |
82779 | 3/1/2023 0:00 |
82790 | 1/1/2023 0:00 |
85145 | 1/30/2023 0:00 |
87673 | 1/20/2023 0:00 |
90930 | 1/1/2023 0:00 |
91137 | 6/1/2023 0:00 |
93634 | 1/30/2023 0:00 |
93785 | 1/1/2023 0:00 |
94605 | 1/1/2023 0:00 |
95672 | 12/8/2022 0:00 |
96195 | 12/21/2022 8:31 |
96284 | 1/1/2023 0:00 |
96412 | 1/1/2023 0:00 |
96416 | 1/1/2023 0:00 |
96421 | 1/1/2023 0:00 |
96422 | 1/1/2023 0:00 |
96423 | 1/1/2023 0:00 |
96424 | 1/1/2023 0:00 |
96426 | 1/1/2023 0:00 |
96427 | 1/1/2023 0:00 |
96429 | 1/1/2023 0:00 |
96431 | 1/1/2023 0:00 |
96432 | 1/1/2023 0:00 |
96434 | 1/1/2023 0:00 |
96435 | 1/1/2023 0:00 |
96436 | 1/1/2023 0:00 |
96437 | 1/1/2023 0:00 |
96842 | 1/1/2023 0:00 |
96847 | 12/27/2022 10:34 |
96875 | 1/1/2023 0:00 |
96990 | 1/1/2023 0:00 |
97096 | 12/1/2022 0:38 |
97099 | 12/1/2022 0:00 |
97104 | 12/1/2022 11:59 |
97106 | 12/1/2022 0:00 |
97109 | 12/1/2022 12:51 |
97128 | 12/1/2022 0:00 |
97146 | 12/1/2022 13:00 |
97152 | 12/1/2022 12:12 |
97158 | 12/1/2022 18:54 |
97199 | 12/1/2022 0:00 |
97200 | 12/1/2022 0:00 |
97201 | 12/1/2022 0:00 |
97202 | 12/2/2022 0:00 |
97204 | 12/2/2022 10:08 |
97214 | 12/2/2022 8:01 |
97251 | 12/2/2022 11:50 |
97252 | 12/2/2022 17:00 |
97257 | 12/2/2022 0:00 |
97266 | 12/2/2022 0:00 |
97269 | 12/2/2022 14:09 |
97270 | 12/2/2022 0:00 |
97271 | 12/2/2022 14:21 |
97280 | 12/2/2022 15:45 |
97283 | 12/2/2022 0:00 |
97287 | 12/1/2022 0:00 |
97308 | 12/2/2022 0:00 |
97322 | 12/4/2022 22:58 |
97328 | 12/2/2022 11:00 |
97331 | 12/5/2022 0:00 |
97339 | 12/12/2022 0:00 |
97341 | 12/19/2022 0:00 |
97342 | 12/1/2022 0:00 |
97354 | 12/5/2022 15:28 |
97357 | 12/5/2022 15:32 |
97360 | 12/1/2022 0:00 |
97381 | 12/5/2022 0:00 |
97403 | 12/1/2022 0:00 |
97409 | 12/5/2022 0:00 |
97415 | 12/1/2022 15:38 |
97416 | 12/5/2022 0:00 |
97417 | 12/5/2022 0:00 |
97418 | 12/5/2022 0:00 |
97422 | 12/5/2022 16:58 |
97423 | 12/5/2022 17:05 |
97424 | 12/5/2022 0:00 |
97433 | 12/5/2022 0:00 |
97436 | 12/6/2022 6:41 |
97437 | 12/6/2022 6:44 |
97438 | 12/2/2022 0:00 |
97439 | 12/2/2022 0:00 |
97440 | 12/2/2022 0:00 |
97443 | 12/1/2022 7:02 |
97444 | 12/1/2022 7:05 |
97445 | 12/6/2022 11:35 |
97447 | 12/1/2022 0:00 |
97448 | 12/1/2022 7:16 |
97449 | 12/6/2022 7:20 |
97451 | 12/6/2022 7:34 |
97456 | 12/3/2022 0:00 |
97464 | 12/6/2022 0:00 |
Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur Thank you so much for your solution and prompt response.
Just want to understand some points:
You are welcome. ABCD is the title of the virtual column. You may give any name instead of ABCD. Glad that the formula is working fine.
@Ashish_Mathur The average are correct. But as it is 12 month rolling average for example Feb 2023, it should go back 11 month adds the counts from March 2022 to Feb 2023 (62) and divide by 12 which will be 5.16666.
As per your explanation it is doing rolling average but not considering 12 month rolling average for example for Feb 2023 it is summing the count from Jan 2022 to feb 2023.
Hi,
You may download my PBI file from here.
Hope this helps.
@Ashish_Mathur Even for
Even for Oct 2022 and Nov 2022 value shouldn't be the same as last 12 month will change for ex. for oct2022(Oct 2022 + last 11 month sum/12) .
Please assist on the same.
Hi , @Swap_nil5502
According to your description, you want to get the "12 months rolling average".
Here are the steps you can refer to :
(1)This is my test data:
To simplify the complexity of creating data, I'm using MAX instead of your COUNT here.
I create a measure to put on the visual:
Max Value(Like your Count of Value) = MAX('Table'[Value])
(2)We can add a measure like this to get it:
12 month rolling average = var _cur_date= MAX('Date'[Date])
var _last_12_month = EOMONTH(_cur_date,-12)+1
var _last_12_month_yearMonth = YEAR(_last_12_month)*100+MONTH(_last_12_month)
var _cur_year_month = MAX('Date'[Year_month])
var _t = SUMMARIZE(ALLSELECTED('Date'),'Date'[Year_month] , "value",[Max Value(Like your Count of Value)])
var _t2 = FILTER(_t ,[Year_month]>= _last_12_month_yearMonth && [Year_month]<= _cur_year_month)
return
AVERAGEX(_t2,[value])
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
I need to use casedatevalue as a slicer and when I select year for ex 2023. It should do the 12 month rolling average from the selected year i.e in this case 2023 and the current month. Example : If I select 2023 from slicer, it should do the rolling average from the current selected year taking into account the current date/month
Hi,
Hi , @Swap_nil5502
You need to add a dimension date table like this:
Date = ADDCOLUMNS( CALENDAR(FIRSTDATE('Table'[Date]) , LASTDATE('Table'[Date])) ,"Year_month", YEAR([Date])*100+ MONTH([Date]))
Then update the dax to this:
1212rollingaverage = var currdate = MAX('Date'[Date])
VAR last12months = EOMONTH(currdate, -12)+1
VAR last12monthyearmonth = YEAR(last12months)*100 + MONTH(last12months)
VAR curryearmonth = MAX('Date'[Year_month])
var t = SUMMARIZE(ALLSELECTED('Date'),'Case'[Year_month],"CaseId",[Countcaseid])
var t2 = FILTER(t,[Year_month] >= last12monthyearmonth && [Year_month] <= curryearmonth)
return
AVERAGEX(t2, [CaseId])
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.(You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
CaseId | CaseDateValue |
22263 | 12/24/2022 0:00 |
22329 | 2/13/2023 0:00 |
22359 | 12/5/2022 0:00 |
72568 | 12/31/2022 0:00 |
77068 | 12/21/2022 0:00 |
78229 | 12/6/2022 0:00 |
78845 | 12/7/2022 0:00 |
78865 | 12/12/2022 0:00 |
81065 | 12/8/2022 0:00 |
82779 | 3/1/2023 0:00 |
82790 | 1/1/2023 0:00 |
85145 | 1/30/2023 0:00 |
87673 | 1/20/2023 0:00 |
90930 | 1/1/2023 0:00 |
91137 | 6/1/2023 0:00 |
93634 | 1/30/2023 0:00 |
93785 | 1/1/2023 0:00 |
94605 | 1/1/2023 0:00 |
95672 | 12/8/2022 0:00 |
96195 | 12/21/2022 8:31 |
96284 | 1/1/2023 0:00 |
96412 | 1/1/2023 0:00 |
96416 | 1/1/2023 0:00 |
96421 | 1/1/2023 0:00 |
96422 | 1/1/2023 0:00 |
96423 | 1/1/2023 0:00 |
96424 | 1/1/2023 0:00 |
96426 | 1/1/2023 0:00 |
96427 | 1/1/2023 0:00 |
96429 | 1/1/2023 0:00 |
96431 | 1/1/2023 0:00 |
96432 | 1/1/2023 0:00 |
96434 | 1/1/2023 0:00 |
96435 | 1/1/2023 0:00 |
96436 | 1/1/2023 0:00 |
96437 | 1/1/2023 0:00 |
96842 | 1/1/2023 0:00 |
96847 | 12/27/2022 10:34 |
96875 | 1/1/2023 0:00 |
96990 | 1/1/2023 0:00 |
97096 | 12/1/2022 0:38 |
97099 | 12/1/2022 0:00 |
97104 | 12/1/2022 11:59 |
97106 | 12/1/2022 0:00 |
97109 | 12/1/2022 12:51 |
97128 | 12/1/2022 0:00 |
97146 | 12/1/2022 13:00 |
97152 | 12/1/2022 12:12 |
97158 | 12/1/2022 18:54 |
97199 | 12/1/2022 0:00 |
97200 | 12/1/2022 0:00 |
97201 | 12/1/2022 0:00 |
97202 | 12/2/2022 0:00 |
97204 | 12/2/2022 10:08 |
97214 | 12/2/2022 8:01 |
97251 | 12/2/2022 11:50 |
97252 | 12/2/2022 17:00 |
97257 | 12/2/2022 0:00 |
97266 | 12/2/2022 0:00 |
97269 | 12/2/2022 14:09 |
97270 | 12/2/2022 0:00 |
97271 | 12/2/2022 14:21 |
97280 | 12/2/2022 15:45 |
97283 | 12/2/2022 0:00 |
97287 | 12/1/2022 0:00 |
97308 | 12/2/2022 0:00 |
97322 | 12/4/2022 22:58 |
97328 | 12/2/2022 11:00 |
97331 | 12/5/2022 0:00 |
97339 | 12/12/2022 0:00 |
97341 | 12/19/2022 0:00 |
97342 | 12/1/2022 0:00 |
97354 | 12/5/2022 15:28 |
97357 | 12/5/2022 15:32 |
97360 | 12/1/2022 0:00 |
97381 | 12/5/2022 0:00 |
97403 | 12/1/2022 0:00 |
97409 | 12/5/2022 0:00 |
97415 | 12/1/2022 15:38 |
97416 | 12/5/2022 0:00 |
97417 | 12/5/2022 0:00 |
97418 | 12/5/2022 0:00 |
97422 | 12/5/2022 16:58 |
97423 | 12/5/2022 17:05 |
97424 | 12/5/2022 0:00 |
97433 | 12/5/2022 0:00 |
97436 | 12/6/2022 6:41 |
97437 | 12/6/2022 6:44 |
97438 | 12/2/2022 0:00 |
97439 | 12/2/2022 0:00 |
97440 | 12/2/2022 0:00 |
97443 | 12/1/2022 7:02 |
97444 | 12/1/2022 7:05 |
97445 | 12/6/2022 11:35 |
97447 | 12/1/2022 0:00 |
97448 | 12/1/2022 7:16 |
97449 | 12/6/2022 7:20 |
97451 | 12/6/2022 7:34 |
97456 | 12/3/2022 0:00 |
97464 | 12/6/2022 0:00 |
I need to use casedatevalue as a slicer and when I select year for ex 2023. It should do the 12 month rolling average from the selected year i.e in this case 2023 and the current month. Example : If I select 2023 from slicer, it should do the rolling average from the current selected year taking into account the current date/month
Hi @Swap_nil5502 ,
I have done a similar thing. Have a look at the below and let me know if it solves your issue. ( it might need some adaptation).
1.Create a calculated table with the dates that will be used as a slicer
SlicerTable = VALUES(SourceTable[Date])
2.Do a measure in your IDs table.
12MonthsAverage =
VAR SelectedDate = SELECTEDVALUE(SlicerTable[Date])
VAR CurrentDate = SELECTEDVALUE(IDsTable[Date])
RETURN IF(CurrentDate <= SelectedDate &&
CurrentDate > EOMONTH(SelectedDate,-12),
Count(IDsTable[CaseId]),0)
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |