December 20, 2008 at 8:04 pm
Hi All,
What would be the correct MDX syntax to calculate the average over a custom date range? For example, I have [Measures].[Sales] and I need to calculate its average from 12-01-2008 to 12-12-2008. Or, another example, I need to calculate average Sales for 12-01-2008, 12-02-2008, 12-03-2008, 12-10-2008, and 12-15-2008?
I have SSAS 2005 Standard Edition.
Thanks you in advance for your input!
--
Pasha
December 22, 2008 at 6:12 am
Hi,
Please go through following link.
It might be solved your problem.
Thanks.
Regards,
Joyal Jhaveri
December 22, 2008 at 11:22 pm
It is not exactly what I need, but thank you anyways. I have found the solution by utilizing "WITH MEMBER CustomAvg AS..."
Thanks,
Pasha
December 24, 2008 at 5:32 am
What front-end tool are you using where you need to be able to create the custom ranges? Are you providing the end-users a way to select the dates or are you doing this on your own? The formula for the calculation is fairly straight forward, but making it more dynamic is where I would just need to know how the end-users are working with the calculation.
with member measures.CustomAvg as sum({[Date].[Calendar].[Date].&[20081201]:
[Date].[Calendar].[Date].&[20081212]},measures.[Sales])/
count({[Date].[Calendar].[Date].&[20081201]:
[Date].[Calendar].[Date].&[20081212]})
OR
with set [Date Range] as ({[Date].[Calendar].[Date].&[20081201], [Date].[Calendar].[Date].&[20081202], [Date].[Calendar].[Date].&[20081203], [Date].[Calendar].[Date].&[20081210], [Date].[Calendar].[Date].&[20081215]}
member measures.CustomAvg as sum([Date Range],measures.[Sales])/
count([Date Range])
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply