cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Moving average of a percentage Measure

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

Accepted Solutions
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

## Re: Moving average of a percentage Measure

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

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

## Re: Moving average of a percentage Measure

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.

Frequent Visitor

## Re: Moving average of a percentage Measure

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 :

Super User

## Re: Moving average of a percentage Measure

Hi,

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

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

Frequent Visitor

Thank's !

It's here

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.

Frequent Visitor

Hi,