Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Swap_nil5502
Helper I
Helper I

12 Months rolling average

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 !

Swap_nil5502_0-1675765695179.png

 

20 REPLIES 20
achanikya
Helper I
Helper I

achanikya_0-1676269939787.png

 

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.

Ashish_Mathur
Super User
Super User

Hi,

Share data in a format the can be pasted in an MS Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

CaseIdCaseDateValue
2226312/24/2022 0:00
223292/13/2023 0:00
2235912/5/2022 0:00
7256812/31/2022 0:00
7706812/21/2022 0:00
7822912/6/2022 0:00
7884512/7/2022 0:00
7886512/12/2022 0:00
8106512/8/2022 0:00
827793/1/2023 0:00
827901/1/2023 0:00
851451/30/2023 0:00
876731/20/2023 0:00
909301/1/2023 0:00
911376/1/2023 0:00
936341/30/2023 0:00
937851/1/2023 0:00
946051/1/2023 0:00
9567212/8/2022 0:00
9619512/21/2022 8:31
962841/1/2023 0:00
964121/1/2023 0:00
964161/1/2023 0:00
964211/1/2023 0:00
964221/1/2023 0:00
964231/1/2023 0:00
964241/1/2023 0:00
964261/1/2023 0:00
964271/1/2023 0:00
964291/1/2023 0:00
964311/1/2023 0:00
964321/1/2023 0:00
964341/1/2023 0:00
964351/1/2023 0:00
964361/1/2023 0:00
964371/1/2023 0:00
968421/1/2023 0:00
9684712/27/2022 10:34
968751/1/2023 0:00
969901/1/2023 0:00
9709612/1/2022 0:38
9709912/1/2022 0:00
9710412/1/2022 11:59
9710612/1/2022 0:00
9710912/1/2022 12:51
9712812/1/2022 0:00
9714612/1/2022 13:00
9715212/1/2022 12:12
9715812/1/2022 18:54
9719912/1/2022 0:00
9720012/1/2022 0:00
9720112/1/2022 0:00
9720212/2/2022 0:00
9720412/2/2022 10:08
9721412/2/2022 8:01
9725112/2/2022 11:50
9725212/2/2022 17:00
9725712/2/2022 0:00
9726612/2/2022 0:00
9726912/2/2022 14:09
9727012/2/2022 0:00
9727112/2/2022 14:21
9728012/2/2022 15:45
9728312/2/2022 0:00
9728712/1/2022 0:00
9730812/2/2022 0:00
9732212/4/2022 22:58
9732812/2/2022 11:00
9733112/5/2022 0:00
9733912/12/2022 0:00
9734112/19/2022 0:00
9734212/1/2022 0:00
9735412/5/2022 15:28
9735712/5/2022 15:32
9736012/1/2022 0:00
9738112/5/2022 0:00
9740312/1/2022 0:00
9740912/5/2022 0:00
9741512/1/2022 15:38
9741612/5/2022 0:00
9741712/5/2022 0:00
9741812/5/2022 0:00
9742212/5/2022 16:58
9742312/5/2022 17:05
9742412/5/2022 0:00
9743312/5/2022 0:00
9743612/6/2022 6:41
9743712/6/2022 6:44
9743812/2/2022 0:00
9743912/2/2022 0:00
9744012/2/2022 0:00
9744312/1/2022 7:02
9744412/1/2022 7:05
9744512/6/2022 11:35
9744712/1/2022 0:00
9744812/1/2022 7:16
9744912/6/2022 7:20
9745112/6/2022 7:34
9745612/3/2022 0:00
9746412/6/2022 0:00

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  Thank you so much for your solution and prompt response.

 

Just want to understand some points:

 

1. "ABCD",[Case count]),[ABCD]) . Couls you Please explain what is "ABCD" as it's not table or column or measure in the file.
2.  As discussed, as its is showing rolling average correctly as expected. As Discussed, if I select date as slicer and select 2023 or 2022, then will it give me last 12 month rolling average from the current month of 2023?

Swap_nil5502_0-1676281815393.png

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  Request your kind consideration.

@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. 

Swap_nil5502_0-1676351241411.png

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur  Even for 

Swap_nil5502_1-1676351324081.png

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.

v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1675826514216.png

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])

vyueyunzhmsft_1-1675826582841.png

 

(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])

 

vyueyunzhmsft_2-1675826612455.png

 

 

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,

 

1212rollingaverage = var currdate = MAX('Case'[CaseDateValue])
VAR last12months = EOMONTH(currdate, -12)+1
VAR last12monthyearmonth = YEAR(last12months)*100 + MONTH(last12months)
VAR curryearmonth = MAX('Case'[Yearmonth])
var t = SUMMARIZE(ALLSELECTED('Case'),'Case'[Yearmonth],"CaseId",[Countcaseid])
var t2 = FILTER(t,'Case'[Yearmonth] >= last12monthyearmonth && 'Case'[Yearmonth] <= curryearmonth)
return
AVERAGEX(t2, [CaseId])
 
The data that has been shown after the measure is blank. 

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]))

 

vyueyunzhmsft_0-1675842139064.png

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

 

CaseIdCaseDateValue
2226312/24/2022 0:00
223292/13/2023 0:00
2235912/5/2022 0:00
7256812/31/2022 0:00
7706812/21/2022 0:00
7822912/6/2022 0:00
7884512/7/2022 0:00
7886512/12/2022 0:00
8106512/8/2022 0:00
827793/1/2023 0:00
827901/1/2023 0:00
851451/30/2023 0:00
876731/20/2023 0:00
909301/1/2023 0:00
911376/1/2023 0:00
936341/30/2023 0:00
937851/1/2023 0:00
946051/1/2023 0:00
9567212/8/2022 0:00
9619512/21/2022 8:31
962841/1/2023 0:00
964121/1/2023 0:00
964161/1/2023 0:00
964211/1/2023 0:00
964221/1/2023 0:00
964231/1/2023 0:00
964241/1/2023 0:00
964261/1/2023 0:00
964271/1/2023 0:00
964291/1/2023 0:00
964311/1/2023 0:00
964321/1/2023 0:00
964341/1/2023 0:00
964351/1/2023 0:00
964361/1/2023 0:00
964371/1/2023 0:00
968421/1/2023 0:00
9684712/27/2022 10:34
968751/1/2023 0:00
969901/1/2023 0:00
9709612/1/2022 0:38
9709912/1/2022 0:00
9710412/1/2022 11:59
9710612/1/2022 0:00
9710912/1/2022 12:51
9712812/1/2022 0:00
9714612/1/2022 13:00
9715212/1/2022 12:12
9715812/1/2022 18:54
9719912/1/2022 0:00
9720012/1/2022 0:00
9720112/1/2022 0:00
9720212/2/2022 0:00
9720412/2/2022 10:08
9721412/2/2022 8:01
9725112/2/2022 11:50
9725212/2/2022 17:00
9725712/2/2022 0:00
9726612/2/2022 0:00
9726912/2/2022 14:09
9727012/2/2022 0:00
9727112/2/2022 14:21
9728012/2/2022 15:45
9728312/2/2022 0:00
9728712/1/2022 0:00
9730812/2/2022 0:00
9732212/4/2022 22:58
9732812/2/2022 11:00
9733112/5/2022 0:00
9733912/12/2022 0:00
9734112/19/2022 0:00
9734212/1/2022 0:00
9735412/5/2022 15:28
9735712/5/2022 15:32
9736012/1/2022 0:00
9738112/5/2022 0:00
9740312/1/2022 0:00
9740912/5/2022 0:00
9741512/1/2022 15:38
9741612/5/2022 0:00
9741712/5/2022 0:00
9741812/5/2022 0:00
9742212/5/2022 16:58
9742312/5/2022 17:05
9742412/5/2022 0:00
9743312/5/2022 0:00
9743612/6/2022 6:41
9743712/6/2022 6:44
9743812/2/2022 0:00
9743912/2/2022 0:00
9744012/2/2022 0:00
9744312/1/2022 7:02
9744412/1/2022 7:05
9744512/6/2022 11:35
9744712/1/2022 0:00
9744812/1/2022 7:16
9744912/6/2022 7:20
9745112/6/2022 7:34
9745612/3/2022 0:00
9746412/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

Padycosmos
Solution Sage
Solution Sage
mircealitoiu
Frequent Visitor

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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.