cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Post Partisan
Post Partisan

Re: Count Of Days In Month.

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

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Count Of Days In Month.

Hi @arvindyadav,

 

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

 

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

Count Of Days In Month.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Highlighted
Super User IV
Super User IV

Re: Count Of Days In Month.

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

---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Partisan
Post Partisan

Re: Count Of Days In Month.

 

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.

 

Date.png

Regards,

Arvind

Highlighted
Super User IV
Super User IV

Re: Count Of Days In Month.

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Post Partisan
Post Partisan

Re: Count Of Days In Month.

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

Highlighted
Solution Sage
Solution Sage

Re: Count Of Days In Month.

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

 

 

Capture.PNGCapture2.PNG

 

 

Highlighted
Post Partisan
Post Partisan

Re: Count Of Days In Month.

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

View solution in original post

Highlighted
Microsoft
Microsoft

Re: Count Of Days In Month.

Hi @arvindyadav,

 

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

 

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

Count Of Days In Month.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Post Partisan
Post Partisan

Re: Count Of Days In Month.

Hi Dale,

 

Thanks.

 

Reagrds, 

Arvind

 

Highlighted
Microsoft
Microsoft

Re: Count Of Days In Month.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors