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
Anshenterprices
Helper IV
Helper IV

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

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

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