Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |