Rolling average for last 12 month

[Anshenterprices]

Hello ,

I want rolling average based on some cretriya.

We need average based on round column and date .

Month year   cost.    Round column

Jan.      2020.   10 

Feb.     2020.    20

Mar.     2020.   40

Apr.      2020.    30.     1

May.     2020.    30

Jun.       2020.    40

July.       2020.    39.     2

Aug.      2020.     20.  

Sep.      2020.     19

Oct.      2020.     12.    3

Nov.     2020.      10 

Dec.     2020.      10 

Jan.     2021.       20

Feb.     2021.       30

Mar.    2021.        39

Apr.     2021.       30.    4 

Like same for all years .

In above data round 1 on showing April 2020 , for that I want last 12 month rolling average.

Like I want rolling average of April 2020 to past 12 month data based on round .

Like every rond I want last 12 month rolling average data for round.

Thank you 

[v-yetao1-msft]

Hi @Anshenterprices 

Please provide the wrong visual and related visual fields settings .

Best Regards

Community Support Team _ Ailsa Tao

[Anshenterprices]

Hello team ,

Below are details information of my dummy data.

I want  last 12 month rolling average based on round column and average rating based on round Column.

Below are some dummy example.

With 2 tabels

Table 1 

Date.             Round.     Rating

1 Jan 2020.     1.              3

2 Feb 2020.      1.             2

3 mar 2020.          

4 apr 2020.       

5 may 2020       2.            4

6 June 2020.      2.            4

7 jul 2020 

8 aug 2020.     

9 sept. 2020.      3.            7

10 Oct 2020.      

11 Nov 2020.    

12 Dec.  2020

1 Jan 2021         4           8 

2 feb 2021.        4.          8

Table 2

Date.        Cost 

1 Jan 20.     100 

2 feb 20.      200

3 mar 20.     300

4 apr 20.     500

5 may 20.    100 

6 jun 20.      200

7 jul 20.        300

8 aug 20.       100 

9 sep 20.       200

10 Oct 20.     300

11 Nov 20.     200

12 Dec 20.      100 

1 Jan 21.         200 

2 feb 21           100 

Need below results.

Round -   avg rating.   Cost 

1.              2.5.              Last 12 month avg 

2.              4                  last 12 month avg

3.               7.       Last 12 m rolling avg 

4.               8.        216 

So based on above I have year filter 

And in matrix I want above results

Like round 4 I want 8 as a rating average

And 216 is last 12 month rolling average.

Please help me to write dax .

Thank you 

[v-yetao1-msft]

Hi @Anshenterprices 

I don't particularly understand your needs .

Do you want to use the Round column value as the selection criteria ? If you choose 1, then calculate the average value of 1 corresponding to April 2020 and the previous 12 months. If you choose 2, then calculate the average value of 12 months before July 2020, isn't it ?

I create a sample based on my own ideas .

(1)Create a column to convert [Month] and [Year] to a date format column[Date]

Date = DATEVALUE('Table'[year]&"/"&'Table'[Month])

(2)Create a new table to extract column [Round column] and [Date]

Date = SELECTCOLUMNS('Table',"Date",'Table'[Date],"Round column",'Table'[Round column])

(3)Create a slicer with 'Date'[Round column] to filter the date in ‘Table’ that between last 12 months ,and then to calculate the average .

rolling average cost = CALCULATE(AVERAGE('Table'[cost]),FILTER('Table','Table'[Date]<=MAX('Date'[Date]) && 'Table'[Date]>=EOMONTH(MAX('Date'[Date]),-12)+1))

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[Anshenterprices]

Thank you for replying....i want round in x Axis of chart and showing rolling average for the same. But whenever I select any month on filters then  round  values showing wrongly in x Axis..

Please suggest me some idea.

[daxer-almighty]

Here are all the calculations that you need:

Standard time-related calculations – DAX Patterns