Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I need to create a moving average over 6 months of percentage.
For example, in september my moving average should be efficiency of september + august + july + june + may + april /6
In August, it should be august + july + june + may + april + march /6...
I tried the solution found by Preetish_1 :
but it didnt work for me.
I have two tables :
- Table_1 contains my Efficiency in % and id_Time
- Table_2 contains my dimTime with year col, month col and date col
Could you help me guys ? 🙂
Thank you
Valentin
Solved! Go to Solution.
Hi,
It's no surprise that your formula is not working. There is no date column in your FACT_SRL table. You must have a date column there and that date column should bear a relationship with the Caldate column of the SUM_Temps table. Only then will the Time Intelligence functions such as DATESBETWEEN() or DATESINPREIOD() work.
Thank you for your replies @v-qiuyu-msft & @Ashish_Mathur🙂
But I try both of yours solutions and both didn't work for me 😕
I forgot to precise, my "Efficiency" is a calculated measure from a rate between two values ("allocated time"/"passed time")
Another thing which could be a problem : when I calculate LASTDATE(CalDate), it returns (2017-12-01) because it's my last date in my dimTime.. But my last values are in september !
Anyway it seems to ignore the time period when it calculates.
My dimTime & relation between two tables :
Hi,
Ensure that you drag the year and month from the Dim_Temps Table.
Yes, it's the year and month from Dim_Temps Table.
How can I attach my pbix file ?
Upload it to Google Drive/OneDrive and share the download link here.
Hi,
It's no surprise that your formula is not working. There is no date column in your FACT_SRL table. You must have a date column there and that date column should bear a relationship with the Caldate column of the SUM_Temps table. Only then will the Time Intelligence functions such as DATESBETWEEN() or DATESINPREIOD() work.
Hi,
Thank you for your reply !
Ok, I thought it will work with my relation based on ID_Temps.. 😕
Hi @Anonymous,
Based on the dataset shared by @v-qiuyu-msft, this will also work
=CALCULATE(AVERAGEX ('Table1','Table1'[Efficiency]),DATESBETWEEN('Table2'[Date],EDATE(MIN('Table2'[Date]),-5),max(Table2[Date])))
Hope this helps.
Hi @Anonymous,
You can create a measure below:
Moving_Average_6_Months =
CALCULATE (
AVERAGEX ( 'Table1', 'Table1'[Efficiency]) ,
DATESINPERIOD (
'Table2'[Date],
LASTDATE ( 'Table2'[Date]),
-6,
MONTH
)
)
Best Regards,
Qiuyun Yu
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |