topic Re: Calculate month end dates between two dates in DAX Commands and Tips

Calculate month end dates between two dates

paultaylor006 — Fri, 16 Sep 2022 10:02:28 GMT

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?

Re: Calculate month end dates between two dates

johnt75 — Fri, 16 Sep 2022 10:16:57 GMT

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

Re: Calculate month end dates between two dates

paultaylor006 — Thu, 22 Sep 2022 08:50:27 GMT

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)