cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anshenterprices
Helper III
Helper III

Rolling average for last 12 month

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 

 

 

 

 

 

 

 

 

5 REPLIES 5
Ailsa-msft
Community Support
Community Support

Hi @Anshenterprices 

Please provide the wrong visual and related visual fields settings .

 

Best Regards

Community Support Team _ Ailsa Tao

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

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 

 

 

 

 

 

           

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Ailsa-msft
Community Support
Community Support

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 :

Ailsamsft_0-1627364395897.pngAilsamsft_1-1627364395900.png

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.

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
Solution Sage
Solution Sage

Here are all the calculations that you need:

Standard time-related calculations – DAX Patterns

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.