powerbidaxcalculated-columnsmoving-averagecalculated-field

How can I calculate a moving average over four weeks using DAX in PowerBI?


I have a table with POS of products and product ID groups with different level of granularities, from item level to department category level.

I want to use DAX for Power BI to add a measure that calculates the moving average of the previous 4 weeks (excluding the current week) (as shown in column "POS for P4W".)

enter image description here

How to do that?


Solution

  • CALCULATE(
        AVERAGE( 'table'[POS] ),
        DATESINPERIOD( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ) - 7, -28, DAY )
    )
    

    This measure takes the average of the POS column over the date period from 7 days before the last date in context through 28 days prior. To implement this measure, you will need a calendar table with a relationship to your POS table.

    ---EDIT-------------------------------

    To aggregate at an item level:

    CALCULATE(
        SUM( 'table'[POS] ) / 4,
        DATESINPERIOD( 'Calendar'[Date], LASTDATE( 'Calendar'[Date] ) - 7, -28, DAY )
    )
    

    This assumes that there are no missing data points for any product/week combinations.