Forum Discussion
How to create a moving average for a merged table in Power Query
This is my data set, daily number of leads and sales. I need to create a rolling 4 week average conversion rate (meaning sales/leads) between the two.
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 )
5 Replies
- Jihwan_Kim
Super User
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 )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.
