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 this number to calculate daily target from a fixed taraget value. 

 

Thank You!

  • 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)

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    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.