Thread: 3 month to date average

1. Registered User
Join Date
Mar 2008
Posts
11

Unanswered: 3 month to date average

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.Curre ntmember, 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)

2. Registered User
Join Date
Mar 2008
Posts
11
i basically managed to get the calculations working using case command , but while being close to the solution, the totals are derived at Month Hirerachy. Inorder for me to get the correct results i need to replicate the same values at date level.
the query which help to get the solution is
CASE WHEN
Sum
(
[Time].[Report Month].CurrentMember.Lag(2) :

[Time].[Report Month].CurrentMember.Lag(1),

[Measures].[Q1 Count]
)
+
Aggregate
(

PeriodsToDate

(

[Time].[Report Time].[Report Month],

[Time].[Report Time].CurrentMember

),

[Measures].[Q1 Count]
) > 0
THEN
(Sum
({
[Time].[Report Month].CurrentMember.Lag(2) :

[Time].[Report Month].CurrentMember.Lag(1)},
{ [Measures].[Q1 Sum]}
)
+
Aggregate
(

PeriodsToDate

({

[Time].[Report Time].[Report Month],

[Time].[Report Time].CurrentMember

}),

{[Measures].[Q1 Sum]}
))
/
(Sum
({
[Time].[Report Month].CurrentMember.Lag(2) :

[Time].[Report Month].CurrentMember.Lag(1)},

{[Measures].[Q1 Count]}
)
+
Aggregate
(

PeriodsToDate

({

[Time].[Report Time].[Report Month],

[Time].[Report Time].CurrentMember

}),

{[Measures].[Q1 Count]}
)
* 100)
ELSE 0 END
But to conclude with i get the value at month level and the date level hierarchy is empty, i am not sure how i can replicate the same value of month level through out the month at date level cell
please refer the post , inorder to view the result at HTML view.
http://forums.microsoft.com/MSDN/ShowPost.aspx?Post ID109042&SiteID=1

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•