Forum Discussion

bparikh's avatar
bparikh
Regular Visitor
5 years ago
Solved

Date difference in Months - Power Query

Hello Experts,   I've two DateTime columns and am trying to find the difference in Months by adding a new column but getting an error. I'm using = Date.Month ([Date1] - [Date2]) Date1 & Date2 bot...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    bparikh 

    if you need the number of months between the dates then add the following, it should work for Date/Time

     = Number.Round(Number.From(([Date 2] - [Date 1])/( 365.25 / 12 )) ,0 )

     

edhans's avatar
edhans
Community Champion
5 years ago

There is no Power Query function like DATEDIFF in DAX, so there are at least two ways to approach this.

 

= Duration.TotalDays(Duration.From([Date2] - [Date1]))

 

That would return the total days. You could then do something like:

 

= Number.IntegerDivide( TheTotalDays, 30)

 

That would give you an approximation of the months. 

If you want to count the actual months, you could do this:

 

                let
                    varTimePeriod1 = Date.Year([Date1]) * 100 + Date.Month([Date1]),
                    varTimePeriod2 = Date.Year([Date2]) * 100 + Date.Month([Date2])
                in
                varTimePriod2 - varTimePeriod1

 

That would turn May 1, 2021 to 202105, and August 15, 2021 to 202108. Then 202108-202105 = 3 months.

But note that it would also return 1 month for a June 30 and July 1 date difference, and 0 months for June 1 and June 30, so it depends on your scenario as to whether the counting days in the first example or just the year/month combo is more relevant to your situation.

thepuzzlemaster's avatar
thepuzzlemaster
Frequent Visitor
3 years ago

This was a great solution, but didn't quite work for me. I made a slight modification for my needs.

With the provided code, there were some issues when comparing a date in Dec to a date in Jan. I wanted those to show up as only 1 month difference.

So instead I calculated the number of months since the year 0, and subtracted the difference between those.


let
today = Date.From(DateTime.LocalNow()),
varTimePeriod1 = Date.Year(today) * 12 + Date.Month(today),
varTimePeriod2 = Date.Year([Date]) * 12 + Date.Month([Date])

in
varTimePeriod2 - varTimePeriod1