MDX_How to ignore filtered date in rolling average

Viewed 40

I have a problem with defining Rolling Average for last 7 days. When the user opens my dashboard ,the default filter for "Date" is set for last 30 days and, this calculated Measure (RollingAverage7Days) is not correct for the first 6 days of filtered date period(because it refers to the days that are filtered). I need to change this mdx formula to ignore the "Date" filter by user:

Sum
    (   
     {
       [DimDate].[Persian Date].CurrentMember.Lag(6)
          : 
         [DimDate].[Persian Date].CurrentMember
    }
     ,[Measures].[SumPrice]
    ) /7

*The green line in the following linechart shows RollingAverage7Days.As you can see for the first 6 days, this calculated measure doesn't have a correct value.

enter image description here Can anybody help me out of that?

0 Answers
Related