How to create a moving average for a merged table in Power Query

[Jacob1832]

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. 

[Jihwan_Kim · Accepted solution]

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 )

[Jacob1832]

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.

[Jacob1832]

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? 

[Jihwan_Kim]

Hi,

Thank you for your message, and please check the below picture and the attached pbix file.

I tried to create a new sample pbix file like below.

I am not sure how your data model looks like, and if the below is not what you are looking for, please share your sample pbix file's link here. And then I can try to look into it to have a more accurate solution.

Rolling avg 4 wks: =
VAR _fourweekstable =
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -28, DAY )
VAR _newtable =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                CALCULATETABLE ( 'Calendar', 'Calendar'[Date] IN _fourweekstable ),
                'Calendar'[Start of Week]
            ),
            "@actuals", [Actuals sum:]
        ),
        [@actuals] <> BLANK ()
    )
VAR _condition =
    COUNTROWS ( _newtable ) >= 4
RETURN
    AVERAGEX ( _newtable, [@actuals] ) * DIVIDE ( _condition, _condition )