Forum Discussion

Jacob1832's avatar
Jacob1832
Frequent Visitor
4 years ago
Solved

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.   
General Comment
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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 )

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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's avatar
Jacob1832
Frequent Visitor
4 years ago

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's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Jacob1832
      Frequent Visitor
      4 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        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 )