Expected Annual Sales (MDX- Analysis Service)

Recently I got a query on to show annual sales.

For example in Adventure works for year 2004 there is sales upto July 31st.



On base of it we want to get expected sales in coming days.

For Ex. upto July 31 total Internet Sales is about $9,770,899.74 in 213 days.

So daily average sales is $9,770,899.74/213 = 45872.768732

so on base of it Sales upto 1St august will be 45872.768732 *214 days
on 2nd August Sales will be 45872.768732 * 215 days

This way we can predicate sales for the remaining days in the year.

Lets see how we can get this

/-----------------------------------------------------------------------/

WITH MEMBER [Measures].[SalesDayCount] AS
sum(ytd(),iif(isempty([Measures].[Internet Sales Amount]),0,1))

MEMBER [Measures].[sumamount] AS
sum(YTD(),[Measures].[Internet Sales Amount])

MEMBER [Measures].[TotalDayCount] AS
sum(YTD(),1)

MEMBER [Measures].[Expected Sales] AS
([Measures].[TotalDayCount]*[Measures].[sumamount])/[Measures].[SalesDayCount]


SELECT {[Measures].[Internet Sales Amount],[Measures].[sumamount],[Measures].[SalesDayCount],[Measures].[TotalDayCount],[Measures].[Expected Sales]} on 0,
DESCENDANTS([Date].[Calendar].[Calendar Year].&[2004],
[Date].[Calendar].[Date],SELF)
ON 1
FROM
[Adventure Works]

/------------------------------------------------------------------------------/