cancel
Showing results for
Did you mean:  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.

Thank you .

1 ACCEPTED SOLUTION  Community Support

Establish a one-to-one relationship between two tables based on date. 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: 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

5 REPLIES 5 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.  Community Support

Establish a one-to-one relationship between two tables based on date. 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: 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  Helper III

Thank you so much ..for exact solution.  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))

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!  Helper III

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  