How to calculate average for time frame which shifts?

Questions & Answers
,
1

Hi all,
I have weekly based Issues created measurment. I need to find average for previous x weeks. So for current week I need to take into account current week and 4 previous weeks; for previous week I need to take into account amount of created issues for previous week and for 4 weeks before previous and so on.
As I see there is a VisibleRowsSet() but Avg() calculates the same value for all time periods (one same digit for the whole column). And this value changes when new day, week, month started, but how can I “store” such history data?
Is it possible somehow calculate average for such time frame which shifts?

Thanks in advance!

2

Hi @pmatolikov!

You can do this with the Lag(…) function.
So, for example, if you have weeks on rows, the following will take the previous four and this week for the set in the Avg(…) function

Avg(
  {
  [Time].CurrentHierarchyMember.Lag(4):
  [Time].CurrentHierarchyMember
  }, [Measures].[Issues created]
)

Lauma / support@eazybi.com