Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.