Forum Discussion
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?
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
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 ResultI 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)
