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".)
How to do that?
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.