Counting Days in MDX (by Mosha Pasumansky)

Time dimension is special in OLAP. Many MDX functions usually only make sense when applied to Time dimension (PrevMember, Lag, ParallelPeriod, PeriodsToDate, ClosingPeriod etc); semiadditive measures work differently with Time etc. Today, however, we will talk about much simpler subject - counting number of days in the currently selected period. There are all kinds of uses for this metric, for example computing averages over time (this is usually interesting in inventory applications to get average level of inventory). For this article we will use Adventure Works cube and compute average of [Internet Sales Amount] over [Ship Date] dimension. We could say that really for computing averages over time, one should use AverageOfChildren semiadditive measure. This is true with two caveats. First, semantics of AverageOfChildren semiadditive aggregation with respect to treating NULLs is the same as with Avg function - i.e. it will not count days which had no sales, and if we wanted to count such days, then AverageOfChildren won't work. Secondly AverageOfChildren is available only in Enterprise Edition. Lastly knowing number of days (or other time periods) in the currently selected time is useful in other calculations as well, we use average because it is simple enough, yet illustrative for our goals.

Read more...