Forum Discussion

arvindyadav's avatar
arvindyadav
Post Partisan
9 years ago
Solved

Count Of Days In Month.

Hi All,   I need to count no of days in given month. i.e, How can I make a column or measure that will automatically go to the calendar of 2016 and input the number of days in the month. So basic...
Need Help
  • arvindyadav's avatar
    arvindyadav
    9 years ago

    Hi ,

     

    DaysInMonthColumn=SWITCH(MONTH([Date]), 1, 31, 2, 29, 3, 31, 4, 30  
               , 5, 31, 6, 30, 7, 31, 8, 31  
               , 9, 30, 10, 31, 11, 30, 12, 31  
               , BLANK() )

    The above formula is useful for me . I also want no of days in 5 months  . So how can I add those number.

     

    Regards,

    Arvind

  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi arvindyadav,

     

    Would you like to try this formula as a calculated column?

     

    DaysInMonth =
DAY ( EOMONTH ( [Date], 0 ) )

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago
DaysInMonthColumn=SWITCH([Month], 1, 31, 2, 29, 3, 31, 4, 30  
               , 5, 31, 6, 30, 7, 31, 8, 31  
               , 9, 30, 10, 31, 11, 30, 12, 31  
               , BLANK() )
arvindyadav's avatar
arvindyadav
Post Partisan
9 years ago

 

Hi,

 

I am getting blank column.

I think you need my data table for it . Please can you provide me answer by judging my data.

Please find the attached Image.

 

Regards,

Arvind

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Maybe:

     

    DaysInMonthColumn=SWITCH(MONTH([Date]), 1, 31, 2, 29, 3, 31, 4, 30  
               , 5, 31, 6, 30, 7, 31, 8, 31  
               , 9, 30, 10, 31, 11, 30, 12, 31  
               , BLANK() )

    ?

     

    Basically, the SWITCH statement evaluates the first parameter, in this case "MONTH([Date])" which should return the numeric value of the month for the given date for each row. 1 for January, 2 for February, etc. The rest of the parameters are in pairs. So, if January then month should return 1 and the SWITCH statement should return 31, for February, 2 and thus 29, and so on.

    • arvindyadav's avatar
      arvindyadav
      Post Partisan
      9 years ago

      Hi ,

       

      Thanks I got another formula for these i.e,

      1)This formula use in to create new column

      DaysinMonth = DAY(

         IF(

             MONTH('Query1'[Date]) = 12,

             DATE(YEAR('Query1'[Date]) + 1,1,1),

             DATE(YEAR('Query1'[Date]),  MONTH('Query1'[Date]) + 1, 1)

         ) - 1

      )

      After above formula please make a measure

      No. of Days = MAX(Query1[DaysinMonth])

      Now you can getting your no of days in a months.

       

      Where Query1 indicate your table name.

       

      Regards,

      Arvind