Daily Average Orders Calculation at Any Level of Date Hierarchy

Today I was helping a fellow BIDN.com user come up with a calculation to figure out the average numbers of orders on day at any level of a date hierarchy. So I figured I’d post the calculation because 1) maybe it will be helpful to you and 2) I’m going to forget this calculation so I’ll at least be able to use this post as a reference.

WITH //Gets a Summation of the Orders in the Fiscal period MEMBER [Measures].[Sum] AS SUM([Date].[Fiscal].CHILDREN,[Measures].[Internet Order Count]) //Counts the days in the Fiscal period MEMBER [Measures].[Count] AS COUNT({DESCENDANTS([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Date])}) //Divides the sum of orders by the count of days to calculate the average daily //orders in that fiscal period MEMBER [Measures].[AvgDailyIntOrdCount] AS SUM([Date].[Fiscal].CHILDREN,[Measures].[Internet Order Count])/ COUNT({DESCENDANTS([Date].[Fiscal].CURRENTMEMBER, [Date].[Fiscal].[Date])}) SELECT {[Measures].[Sum],[Measures].[Count],[Measures].[AvgDailyIntOrdCount]} on 0, {[Date].[Fiscal].Members} ON ROWS FROM [Adventure Works]

In the calculation, I use the SUM function to get an aggregations of the Internet Order Count for any level of the Fiscal Date hierarchy. I also used the Count function with the Descendants function to get a count of days beneath a member of any level of the Fiscal Date hierarchy.

It seems to work pretty good, but if you have an alternative way to calculate this, please post it in the comments section!

Advertisements

One thought on “Daily Average Orders Calculation at Any Level of Date Hierarchy”

  1. Helpful article. Thank you
    I am facing this challenge. Let s say the sales for 10 days (day 1 to day 10) are $1,$2,$3,$4,$5,$6,$7,$8,$9,$10 respectively. So, how to calculate the average for a given day starting from the 1st day (in MDX)? for instance, the average salesuntil day 5 is $ ($1+$2+$3+$4+$5)/5, and so on. Your thoughts are appreciated. Thank you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s