Forum Discussion
How to create a moving average for a merged table in Power Query
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
- I created a sample pbix file like below.
- it is for creating a measure to provide 28days rolling average (daily avg.)
- I created a calendar table to use time-intelligent DAX function -> DATESINPERIOD & LASTDATE
Rolling four weeks avg conversion: = VAR rollingfourweeks = DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -28, DAY ) VAR _condition = COUNTROWS ( rollingfourweeks ) >= 28 RETURN AVERAGEX ( rollingfourweeks, [Conversion measure:] ) * DIVIDE ( _condition, _condition )
Super UserHi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
- I created a sample pbix file like below.
- it is for creating a measure to provide 28days rolling average (daily avg.)
- I created a calendar table to use time-intelligent DAX function -> DATESINPERIOD & LASTDATE
Rolling four weeks avg conversion: =
VAR rollingfourweeks =
DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -28, DAY )
VAR _condition =
COUNTROWS ( rollingfourweeks ) >= 28
RETURN
AVERAGEX ( rollingfourweeks, [Conversion measure:] )
* DIVIDE ( _condition, _condition )
Hi, i have a change in the data set and it is instead by week.
How would i calculate a 4 week moving average in this case?
- Jihwan_Kim
Hi,
Thank you for your feedback.
Please check the below picture and the attached pbix file.
All measures are in the attached pbix file.
Hey, i have one more i need help with.
In this case, the rolling 4 week averagewould be a sum of the Actuals col grouped on the week and state cols, and then divided by 4. Any ideas?
