Calculate Days in Month based on Year/Month to add column to Dates table for use in other formulas

[lsullivan6311]

I need the total number of days in the month based on the Year/Month; for example,

Year/Month            MonthName              MonthNo         NoOfDays

2022/01                    January                           1                      31      

2022/02                    February                         2                      28

so that is the year was a leap year, February would have 29 days.  I will be using a Year/Month filter on visuals and a measure that will need the days in the month to calculate unit hours in that month.  I have the measure I need already, but I just need to update for the filtered number of days.

[ppm1 · Accepted solution]

You can use this expression in a calculated column, provided you have a column with a date value within the desired month.

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

Or in a measure, you can also put any expression that returns a date inside EOMONTH too.

Pat