Forum Discussion

newlearnpbi123's avatar
newlearnpbi123
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 
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

     

Sergii24's avatar
Sergii24
Super User
2 years ago

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's avatar
    newlearnpbi123
    Helper 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