1

I already had an MDX query that returns number of days in a Month Hierarchy using a calculated member. What I needed to do is to add new calculated member that returns number of days excluding weekends (Saturdays and Sundays).

Below is my existing MDX query using Adventure Works DW 2008R2 SE

WITH 
  MEMBER [Measures].[Num of Days] AS
    COUNT ( 
      Descendants ( [Date].[Calendar],  [Date].[Calendar].[Date]) 
    )

SELECT 
  { [Measures].[Num of Days] } ON COLUMNS,  
  [Date].[Calendar].[Month].ALLMEMBERS ON ROWS
FROM [Adventure Works]

sample existing output:

+----------------+-------------+
|   Month Year   | Num of Days |
+----------------+-------------+
| July 2005      |          31 |
| August 2005    |          31 |
| September 2005 |          30 |
+----------------+-------------+

sample expected output:

+----------------+-------------+--------------------------------+
|   Month Year   | Num of Days | Num of Days Excluding Weekends |
+----------------+-------------+--------------------------------+
| July 2005      |          31 |                             21 |
| August 2005    |          31 |                             23 |
| September 2005 |          30 |                             22 |
+----------------+-------------+--------------------------------+
ggarcia
  • 47
  • 1
  • 10

1 Answers1

3

The standard solution for this is to have a hierarchy in your time dimension with the days of the week ( Monday,... Sunday). Once you for this you can filter directly using those members. You can even do a hierarchy with just weekend/no weekend. The best solution depends on the measures you've in your schema, the best is always using standard MDX aggregations and avoiding using SUM/COUNT on a set of members.

Other solutions might depend on how you implemented your hierarchy, you can use the Weekday and filter function.

Honestly something like, I haven't check it

 Count(  FILTER( Descendants ( [Date].[Calendar],  [Date].[Calendar].[Date]) ,  WeekDay( [Date].[Calendar].currentmember.properties("KEY"), 2) <= 5 ) )
ic3
  • 7,917
  • 14
  • 67
  • 115
  • This is the current structure of the Date dimension http://goo.gl/NjTJQ9. It has already [Date].[Day Name] attribute which has members Sunday to Saturday. Can I use this to achieve the expected output? – ggarcia Sep 03 '14 at 12:55
  • Thank you so much for your help. I modified your code a bit and this solves my problem :) . `Count( FILTER( Descendants ( [Date].[Calendar], [Date].[Calendar].[Date]) , WeekDay( [Date].[Calendar].CurrentMember.Member_CAPTION, 2) <= 5 ) )` – ggarcia Sep 03 '14 at 13:53