cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculate month end dates between two dates

I need to count the number of month end dates between two dates. I've tried using DATEDIFF but cannot quite make this work with the added requirement of counting month end dates.

In the example below I've shown the months count results I would expect to see by calculating the month end dates crossed between the two dates. If the start or end date lands on the month end then they count as being crossed.

Start date     End Date     Months count
01/08/22     30/08/22     0
20/08/22     31/08/22     1
31/08/22     01/09/22     1
31/08/22     30/09/22     2
20/08/22     01/09/22     1
20/08/22     30/09/22     2
20/08/22     31/07/23     12

Can anyone suggest a solution to this?

1 ACCEPTED SOLUTION
Frequent Visitor

I found a slightly different solution by creating an end of month column for the start date, and then then using an end of month calc for the end date but adding a day to the end date to push it into the next month if it fell on the month end date. This allowed me to accruately count month ends.

This was the dax I used below and 'StartDate_EndofMonth' iis the  end of month column I created for the start date.

Months count = DATEDIFF(EOMONTH('table'[StartDate_EndofMonth],0), EOMONTH('table'[End Date]+1,0),MONTH)

2 REPLIES 2
Frequent Visitor

I found a slightly different solution by creating an end of month column for the start date, and then then using an end of month calc for the end date but adding a day to the end date to push it into the next month if it fell on the month end date. This allowed me to accruately count month ends.

This was the dax I used below and 'StartDate_EndofMonth' iis the  end of month column I created for the start date.

Months count = DATEDIFF(EOMONTH('table'[StartDate_EndofMonth],0), EOMONTH('table'[End Date]+1,0),MONTH)

Super User

You could add a End of Month column to your date table and then use

Months count =
VAR StartDate =
SELECTEDVALUE ( 'Table'[Start date] )
VAR EndDate =
SELECTEDVALUE ( 'Table'[End date] )
VAR Result =
CALCULATE (
COUNTROWS ( VALUES ( 'Date'[End of month] ) ),
DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
)
RETURN
Result

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors