Calculate month end dates between two dates

[paultaylor006]

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?

[paultaylor006 · Accepted solution]

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)

[johnt75]

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