Dynamic Rolling Average

I am looking to create a dynamic period, over which I can calculate a rolling average.

For example:

Daily usage period = 7

Average sales over period will then be from today, backward looking for 7 days.


The problem is that the period may be different for every sales location, and could be changed daily.

For example:


location: 1234      date: 17.05.2018      Daily usage period: 7

location: 1234      date: 20.05.2018      Daily usage period: 4


location: 9632      date: 17.05.2018      Daily usage period: 7

location: 9632      date: 20.05.2018      Daily usage period: 7


Any suggestions?


I have created an input cube for the daily usage period by location. 


  • ciao Kelly Goldsworthy the Daily usage period is calculated based on what entity?

  • Hi Pietro, the Daily usage period is a manual input and is determined by the model user. So therefore the entity is just

    "Daily usage period". The logic behind why a user would change the period is driven completely outside of this model.

    I hope that clarifies?

  • Have you tryed to use the Advance option in dataflow? You can move back or forth your values based on the cube where you made dataentry.

  • Brilliant - thanks for this. I am going to start working on it now, but I think it is exactly what I was looking for.