Forum Discussion
Rolling 4 Month average on aggregated data
Hello,
I need to calculate rolling 4 month average on data grouped by month-year.
I tried the following formula but it returns incorrect values as you can see in the picture below:
Moving_Average_4_Months =
CALCULATE(AVERAGEX ( SUMMARIZE( 'table1', 'table1'[yearMonth]), AVERAGE('table1'[Elapsed Time] )), DATESINPERIOD (
'table1'[yearMonth],
LASTDATE ( 'table1'[yearMonth] ),
-4,
MONTH))
where
yearMonth = DATE([Opened].[Year],[Opened].[MonthNo],1)You can downlaod the dataset at the following link: https://file.io/3qpNkUZwlOYO.
Can some help me? Thank in advance.
Hi odraode_ ,
Your shared dataset has been deleted. So I created sample data by myself.
We need to calculate the average for four months grouped by ID, rolling.
The measure is below.
Rolling 4 month average = VAR _CURRENT=MAX('Table'[Date]) VAR _START=EOMONTH(_CURRENT,-4)+1 VAR _END=EOMONTH(_CURRENT,0) RETURN CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLSELECTED('Table'),[ID]=MAX('Table'[ID])&&[Date]>=_START&&[Date]<=_END))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi odraode_ ,
Your shared dataset has been deleted. So I created sample data by myself.
We need to calculate the average for four months grouped by ID, rolling.
The measure is below.
Rolling 4 month average = VAR _CURRENT=MAX('Table'[Date]) VAR _START=EOMONTH(_CURRENT,-4)+1 VAR _END=EOMONTH(_CURRENT,0) RETURN CALCULATE(AVERAGE('Table'[Value]),FILTER(ALLSELECTED('Table'),[ID]=MAX('Table'[ID])&&[Date]>=_START&&[Date]<=_END))Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.