Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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 .
Solved! Go to Solution.
Hi, @Anshenterprices
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
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.
Hi, @Anshenterprices
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
Thank you so much ..for exact solution.
@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 .
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |