I recently implemented an MDX expression to count the number of weekdays in a given timeframe. This logic is likely easier to implement in SQL, but in this instance it was necessary to implement in MDX. I thought I’d share the MDX since it may prove to be useful to others.
The MDX expression is included below, I implemented this logic as a ‘Calculated Member’ in a cube.
COUNT(DESCENDANTS([Actual Ship Date].[Calendar Fiscal].CurrentMember,[Actual Ship Date].[Calendar Fiscal].[Date])*{[Actual Ship Date].[Fiscal Day of Week].&[2]:[Actual Ship Date].[Fiscal Day of Week].&[6]}*{NULL:EXISTS([Actual Ship Date].[Date].[Date].MEMBERS,[Actual Ship Date].[Current Day].&[Y])(0)})
‘Actual Ship Date’ is a date dimension in the cube that includes a hierarchy called ‘Calendar Fiscal’.
The ‘Calendar Fiscal’ hierarchy has the following levels:
- Fiscal Year
- Fiscal Quarter
- Fiscal Month
- Fiscal Week
- Date
There is also an attribute named ‘Fiscal Day of Week’ in the ‘Actual Ship Date’ dimension. {[Actual Ship Date].[Fiscal Day of Week].&[2]:[Actual Ship Date].[Fiscal Day of Week].&[6]} represents the range of days from Monday – Friday.
The attribute ‘Current Day’ is set to ‘Y’ for the current date and ‘N’ for all other dates. The logic {NULL:EXISTS([Actual Ship Date].[Date].[Date].MEMBERS,[Actual Ship Date].[Current Day].&[Y])(0)} returns a set that includes all dates through the current date. The Exists function returns a set of tuples of the first specified set that exist with one or more tuples of the second specified set (for more details regarding the Exists function, check out this article). In this scenario, a single Date member is returned where Current Day is set to ‘Y’. It is worth mentioning that this logic is only necessary if you want to exclude future dates from the count.
Finally, the DESCENDENTS function, DESCENDANTS([Actual Ship Date].[Calendar Fiscal].CurrentMember,[Actual Ship Date].[Calendar Fiscal].[Date]), returns a set of descendants at the [Date] level of the hierarchy with ancestor ([Actual Ship Date].[Calendar Fiscal].CurrentMember. For example, if the current member is at the ‘Week’ level, then the count of weekdays should return 5; if the current member is at the ‘Day’ level, then the count will return either 0 or 1.
