Forum Discussion

powerbiuser32's avatar
powerbiuser32
Regular Visitor
8 years ago
Solved

Count number of Days In current Month

Hello,   I need a measure to count the number of days in the current month, without the use of any tables etc.  it should show whether this month has either 30, 31, 28 or 29 days. I want to use th...
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI Anonymous

     

    You could try

     

    Measure 2 = 
VAR t = TODAY()
RETURN  
    COUNTROWS(
        CALENDAR(
            FORMAT(t,"YYYY-MM-01") ,
            EDATE(FORMAT(t,"YYYY-MM-01"),1)-1
           )
    )

    or

     

    Measure 2 = 
VAR s = FORMAT(TODAY(),"YYYY-MM-01")
VAR e = EDATE(FORMAT(TODAY(),"YYYY-MM-01"),1)
RETURN int( e-s)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi powerbiuser32, 

 

Try this measure:

Days_current_month =
DAY (
    IF (
        MONTH ( TODAY () ) = 12;
        DATE ( YEAR ( TODAY () ) + 1; 1; 1 );
        DATE ( YEAR ( TODAY () ); MONTH ( TODAY () ) + 1; 1 )
    )
        - 1
)

Regards.

powerbiuser32's avatar
powerbiuser32
Regular Visitor
8 years ago

Hi Anonymous

 

Thank you. However the code returns a syntax error when copied an pasted.

 

Regards.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    powerbiuser32,

     

    The code works, surely what is happening is that you have Power BI Desktop configured to use commas instead of semicolons as DAX separators, and for this reason Phil_Seamark's code worked without problems (using commas). So you should simply change the semicolons by commas.

    Here you have a tool to detect syntax errors in DAX and format with a recommended indentation, you can modify the separators in US / UK (comma) and Others (semicolon):

    https://www.daxformatter.com

     

    Regards.