Forum Discussion

paultaylor006's avatar
paultaylor006
Frequent Visitor
3 years ago
Solved

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 be...
  • paultaylor006's avatar
    paultaylor006
    3 years ago

    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)

     

paultaylor006's avatar
paultaylor006
Frequent Visitor
3 years ago

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)