How do I calculate the number of days in a month using Power BI?

---

You can use the following syntax in DAX to calculate the number of days in a month:

Days in Month = DAY(EOMONTH('my_data'[Date],0))

This particular formula creates a new column named Days in Month that contains the number of days in the month for the corresponding date in the Date column.

The following example shows how to use this syntax in practice.

Example: How to Calculate Number of Days in Month in Power BI

Suppose we have the following table named my_data that contains information about sales made on various dates by some company:

Suppose that we would like to create a new column that contains the number of days in the month associated with each date in the Date column.

To do so, click the Table tools tab, then click the icon called New column:

Then type the following formula into the formula bar:

Days in Month = DAY(EOMONTH('my_data'[Date],0))

This will create a new column named Days in Month that displays the number of days in the month for each corresponding date in the Date column:

For example, we can see:

• January 1, 2022 is in January, which has 31 days in the month.
• May 14, 2022 is in May, which has 31 days in the month.
• June 19, 2022 is in June, which has 30 days in the month.

And so on.

How This Formula Works

Recall the formula that we used to calculate the number of days in the month:

Days in Month = DAY(EOMONTH('my_data'[Date],0))

First, we used the EOMONTH function to extract the last day of the month from the Date column.

For example, for a date of January 1, 2022 this function would return January 31, 2022.

Then, we used the DAY function to convert this date to a day of the month.

For example, for January 31, 2022 this function would return 31.

We repeat this process for each date in the Date column.

Note: You can find the complete documentation for the EOMONTH function in DAX .

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI: