how to count number of days in a month

Question

I have two calculated members, they both are intended to calculate the number of days in a month

 

CREATE MEMBER CURRENTCUBE.[MEASURES].[Count of Days 1]

AS

COUNT(DESCENDANTS([Date].[Period].CurrentMember, [Date].[Period].[Date])),

VISIBLE = 1;

 

CREATE MEMBER CURRENTCUBE.[MEASURES].[Count of Days 2]

AS

COUNT(EXISTING [Date].[Date].MEMBERS),

VISIBLE = 1;

 

[MEASURES].[Count of Days 1] gives me the correct number of days when I analyze through the time dimension, but otherwise it gives me a wrong number, also, the total is always wrong, it gives me the number of days in all existing periods

 

[MEASURES].[Count of Days 2] gives me the number of days in a month + 1, and the total is also always wrong, it gives me the number of days in all existing periods + 1

 

Any idea of how can I create a calculated member that gives me the number of days in a month and the total is correct?

 

Thanks in Advance

                  Isaac

Answer 1

Hi,

 

This link might help you with this problem:

http://sqlblog.com/blogs/mosha/archive/2007/05/27/counting-days-in-mdx.aspx

 

HTH

Thomas Ivarsson