Forum Discussion

Helper I

2 years ago

Solved

How to calculate rolling 5 week average ?

I have only date column which contains weeks, now i want to calculate rolling 5 week average, i tried dateinperiod could not get it to work. thank you PaulDBrown

Anonymous
2 years ago
Hi Sergii24 ,thanks for the quick reply, I'll add further.

Hi newlearnpbi123 ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a column

Weeknumber = WEEKNUM([Date],2)

2. Use the following DAX expression to create a measure

Measure = VAR _a = SELECTEDVALUE('Table'[Weeknumber]) VAR _b = CALCULATE(AVERAGE('Table'[Unit]),FILTER(ALL('Table'),'Table'[Weeknumber] <= _a && 'Table'[Weeknumber] >= _a -4 )) RETURN _b

3.Final output
(2)2024.5.15.pbix42 KB

Sergii24

Super User

2 years ago

Hi newlearnpbi123, here is an easy method:

Rolling average = 
VAR _NumberOfPeriods = 2                                    //change it to 5 or any other number
VAR _MinPeriod = MIN( 'Table'[Week] )                       //get the minimum week for current filter context
VAR _NumPreviousWeeks =                                     //this measure is required to start calcualtion only when you get last N weeks
    COUNTROWS(
        CALCULATETABLE(
            VALUES( 'Table'[Week] ),
            'Table'[Week] < _MinPeriod
        )
    )
RETURN
    IF(
        _NumPreviousWeeks < _NumberOfPeriods,
        BLANK(),
        CALCULATE(
            AVERAGE( 'Table'[Value] ),                          //calculate the average
            'Table'[Week] >= _MinPeriod - _NumberOfPeriods,     //define min week
            'Table'[Week] < _MinPeriod                          //define max week
        )
    )

Here is the output: