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
Anonymous
Not applicable

Moving average of a percentage Measure

 

2017-09-27 14_50_43-fr5dtcpbdd08v - Connexion Bureau à distance.png

 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

 

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Thank you for your replies @v-qiuyu-msft & @Ashish_Mathur🙂

 

But I try both of yours solutions and both didn't work for me 😕

moving_average.png

 

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 :

dimtime.pngrelations.png

Hi,

 

Ensure that you drag the year and month from the Dim_Temps Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Thank's !

 

It's 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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi,

 

Thank you for your reply !

 

Ok, I thought it will work with my relation based on ID_Temps.. 😕

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-qiuyu-msft
Community Support
Community Support

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

 

q3.PNG

 

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.