Forum Discussion

newlearnpbi123's avatar
newlearnpbi123
Icon for Helper I rankHelper 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 
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    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

     

newlearnpbi123's avatar
newlearnpbi123
Icon for Helper I rankHelper I
2 years ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
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