Calculate number of months between two dates in PowerQuery

[Anonymous]

How do I calculate the number of months between two dates in PowerQuery? I can figure it out in DAX but can't seem to find the syntax to make it work in PowerQuery as a custom coloumn.

For example:

Start Date: 01/12/2020

End Date: 01/03/2020

= 4 months

[Krishna_]

I know this is very old thread, i've landed here searching for a quick solution. 

I found none are really useful. So i've figured out by myself. Below is the solution.

Number.Abs(Date.Year(Date.EndOfMonth([To]))+Date.Month(Date.EndOfMonth([To]))/12-Date.Year([From])-Date.Month([From])/12)*12 + 1

[Anonymous]

All the answers here are bad.

Correct answer can be see in this questions comments (User lbendlin:)

https://community.powerbi.com/t5/Power-Query/Date-difference-in-Months-Power-Query/td-p/1993216

Extract from that:

Date.Year([Date2])*12+Date.Month([Date2])-Date.Year([Date1])*12-Date.Month([Date1])

[Ashish_Mathur]

Hi,

See if this helps - Generating Rows by Month for Date Ranges in Power Query | by Daniel Marsh-Patrick | Daniel Marsh-Pat...

[mahoneypat]

The first one works across years.  Please share that part of your M code to troubleshoot.

Regards,

Pat

[mahoneypat · Accepted solution]

It is easiest to use DATEDIFF with MONTH on the DAX side, but in Power Query you can use the formula below.  It isn't exact but may work for you.

= Duration.TotalDays([EndDate]-[StartDate])/30

If within the same year, you can use Date.Month([EndDate]) - DateMonth([StartDate])

Regards,

Pat

[Anonymous]

Thanks @mahoneypat , I've tried both those solutions but they don't work as it's not accurate and goes over various years.