cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
valentin1402 Frequent Visitor
Frequent Visitor

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 ? Smiley Happy

 

Thank you

 

Valentin

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Moving average of a percentage Measure

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.

9 REPLIES 9
Moderator v-qiuyu-msft
Moderator

Re: Moving average of a percentage Measure

Hi @valentin1402,

 

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.
Super User
Super User

Re: Moving average of a percentage Measure

Hi @valentin1402,

 

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.

valentin1402 Frequent Visitor
Frequent Visitor

Re: Moving average of a percentage Measure

Thank you for your replies @v-qiuyu-msft & @Ashish_MathurSmiley Happy

 

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

Super User
Super User

Re: Moving average of a percentage Measure

Hi,

 

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

valentin1402 Frequent Visitor
Frequent Visitor

Re: Moving average of a percentage Measure

Yes, it's the year and month from Dim_Temps Table.

 

How can I attach my pbix file ?

Super User
Super User

Re: Moving average of a percentage Measure

Upload it to Google Drive/OneDrive and share the download link here.

valentin1402 Frequent Visitor
Frequent Visitor

Re: Moving average of a percentage Measure

Thank's !

 

It's here

Super User
Super User

Re: Moving average of a percentage Measure

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.

Highlighted
valentin1402 Frequent Visitor
Frequent Visitor

Re: Moving average of a percentage Measure

Hi,

 

Thank you for your reply !

 

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