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

Rolling average for last 12 month

Hello team ,

 

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 .

 

 

 

 

 

           

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @Anshenterprices 

 

Establish a one-to-one relationship between two tables based on date.

vangzhengmsft_1-1627881450713.png

Then create 2 measures like this:

_Avg_rating = 
CALCULATE(
    AVERAGE(Table1[Rating]),
    ALLEXCEPT(Table1,Table1[Round]))
_Avg_Cost = 
var _date=MAX('Table2'[Date])
var _sum=CALCULATE (
    SUM ( Table2[Cost] ),
    FILTER (
        ALL ( Table2 ),
        [Date] >= EOMONTH(_date,-12)
            && [Date] <= MAX ( [Date] )
    )
)
var _avg=DIVIDE(_sum,12)
return _avg

So we can create a matrix visual when round is not blank.

Result:

vangzhengmsft_0-1627881413035.png

 

Please refer to the attachment below for details

 

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

5 REPLIES 5
Hitesh_sri
Frequent Visitor

First, create a DAX for average rating from Table 1.

Average_rating = CALCULATE(AVERAGE(Table1[Rating]), ALLEXCEPT(Table1,Table1[Round]))

Second,  calculate the average cost by defining variables and getting average cost by dividing by 12

Average_Cost = var _date=MAX('Table2'[Date])
var _sum = CALCULATE(SUM(Table2[Cost]), FILTER(ALL(Table2), [Date]>=EOMONTH(_date,-12)&& [Date] <= MAX([Date])))
var _avg=DIVIDE(_sum,12)
return _avg

Now, create a matrix by adding Rounds, average rating and average cost.

 

v-angzheng-msft
Community Support
Community Support

Hi, @Anshenterprices 

 

Establish a one-to-one relationship between two tables based on date.

vangzhengmsft_1-1627881450713.png

Then create 2 measures like this:

_Avg_rating = 
CALCULATE(
    AVERAGE(Table1[Rating]),
    ALLEXCEPT(Table1,Table1[Round]))
_Avg_Cost = 
var _date=MAX('Table2'[Date])
var _sum=CALCULATE (
    SUM ( Table2[Cost] ),
    FILTER (
        ALL ( Table2 ),
        [Date] >= EOMONTH(_date,-12)
            && [Date] <= MAX ( [Date] )
    )
)
var _avg=DIVIDE(_sum,12)
return _avg

So we can create a matrix visual when round is not blank.

Result:

vangzhengmsft_0-1627881413035.png

 

Please refer to the attachment below for details

 

 

Hope this helps.

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

Thank you so much ..for exact solution.

amitchandak
Super User
Super User

@Anshenterprices , you need to have common date table, and from that you have filter. but last 2 month avg will be same for all rows

 

 

Rolling 12 = CALCULATE(Average(Table2[cost]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

or

 

 

Rolling 12 = CALCULATE(sum(Table2[cost]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))/12

 

or

 

 

Rolling 12 =

var _1 = AverageX(values(Date[Month year]),calculate(sum(Table2[cost])))

return

CALCULATE(_1,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Hello , thank you for replying.

 

I need average formula based on round column...like round is my x Axis and rolling avg of last 12 month is my y Axis.

 

Like in my results... Round is 4 avg rating is 8 and last 12 month is 216 

 

I want avg relate to round column 

 

Please help me to write dax .

@amitchandak 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!