cancel
Showing results for
Did you mean:
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
Post Partisan

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

Microsoft

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

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

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.
9 REPLIES 9
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

Please give Kudos or Mark as a Solution!

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

Post Partisan

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

Microsoft

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

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

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.
Post Partisan

Hi Dale,

Thanks.

Reagrds,

Arvind

Microsoft

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Partisan

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.

Regards,

Arvind

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Partisan

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

Announcements

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.

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!

2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors