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.
