Count Of Days In Month.

[arvindyadav]

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 basically for every row in January I want it to have 31 days for 2016; February I want 29 days for 2016, etc.? 

Regards,

Arvind

[arvindyadav · Accepted solution]

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

[fhill]

I took a less technical approach to this solution.  I imported a list of all dates for 3 years from Excel.  (Make it as long as you need.)  I had excel format the date as YYYY-MM-DD before importing.  ** Make sure to convert your date column back to TEXT for now.**  I splt the column by the Right Most '-' delimeter.  This gives me my first screen shot below.  

I now Grouped the data by change in 'Post Split Date' and Counted the rows as a result.  I then changed the Post Split (currently showing YYYY-MM as TEXT ) back into a date and Power BI was nice enough to assign it to the frist of each month automatically.

I now have a Month that can be queried or referenced and the Number of Days in that month for as long out / back as your original excel import.  FOrrest

[arvindyadav · Accepted solution]

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 · Accepted solution]

Hi @arvindyadav,

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

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

Best Regards!

Dale

[arvindyadav]

Hi Dale,

Thanks.

Reagrds, 

Arvind

[v-jiascu-msft]

Hi @arvindyadav,

Could you please mark the proper answer as solution if it's convenient for you? That will be a help to the others.

Best Regards!
Dale

[Greg_Deckler]

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]

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]

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]

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