Hello Friends
Actually i posted this problem earlier , hoping that i got the result , did not look in depth untill i realized the values i got was getting was wrong.
The Scenario is still the same.
3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.
I am creating a Calculated Member as measure.
Basically if i select the average on 76th day of the year, it should basically be the average of 76th Day ( well offcourse whould ignore the empty cells )
For Example the result set should be very similar , It is basically doing MTD
Running balance is basically adding up , where as 3 month to date average should be average of 3 month previous average
suppose today is 75th day , so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)
I have used the lag function
AVG(Hirearchy.Currentmember.lag(2):Hirearchy.Currentmember, Measure.abc)
where hirerachy is Year->Month-date
The problem i have here is which i am coming close to conclusion
When i use month level it gives me average of month level , the result on month level is fine. But my requirment is to have it on date level. but how do i have rolling average of 3 month in a date level, if i do a date level with 90 days lag which is not correct which is average of 90 days from current day.
AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Ab1_Avg]
)
When i drill down to date level , which would be assumed
AVG([Tbl Date Key].[Report Hirerachy].date.Lag(2):[Tbl Date Key].[Report Hirerachy].date,[Measures].[Ab1_Avg]
)
its doing a lag on 3 days lag as appose to , I need the lag on 3 months on a day which would be 90 days
should i be doing a lag on days.
The problem at 90 days lag would be every it would lag 90 days average , but what i am looking for it is when it is on the middle of the month it should be
suppose today is 15th day of month, so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)
i am confused , please help