cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arvindyadav
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

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

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
fhill
Resident Rockstar
Resident Rockstar

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

 

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to be a Super User helping give back to the community!
Thank You!




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

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.

Hi Dale,

 

Thanks.

 

Reagrds, 

Arvind

 

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.
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

 

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.