Forum Discussion
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
- Anonymous2 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 columnWeeknumber = 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 _b3.Final output
5 Replies
- Sergii24
Super User
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:
Feel free to adjust formula parameters as needed.
Good luck!- newlearnpbi123
Helper I
thank you sergii, but i am getting blank
- Sergii24
Super User
You can upload sample pbix used by for the example here. If same calculation provides blank(), than there is something in your semantic model that should be taken into account. However, I hope that by having this pbix as example and making research online you can find the answer! If not, please share your pbix sample with your semantic model.
- newlearnpbi123
Helper I
sorry i cannot upload the pbi file for the obvious reasons,
i just have 3 column one is category and other is weeks and other is unit ( above screenshot )any help appreciated sergii, thank you.
- AnonymousNot applicable
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 columnWeeknumber = 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 _b3.Final output