CODEWITHSUNDEEP
powerbipowerquerym
Thao N
Thao N

Reputation: 131

How to use DATEDIFF in Power Query (M query) to look for months between two dates

I need to find the month difference between two dates (checkIn and Checkout dates) in Power Query (M-query). It can be similar to DAX bellow.

period of months = DATEDIFF([dateCheckIn], [dateCheckOut], MONTH )

I found the function daysDiff = each Duration.days([date1]-[date2]) but there is no function for month difference.

Upvotes: 10

Views: 68161

Answers (5)

Mina Ghazawy
Mina Ghazawy

Reputation: 1

You can use this formula:

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

Upvotes: 0

DaccioZilla
DaccioZilla

Reputation: 1

I think you could use Number.From(([Date1] - [Date2])/30).

Upvotes: 0

Chi1ink
Chi1ink

Reputation: 1

I thought this help me so if you like:

(12-Date.Month([StartDate])+1)+
(Date.Month([EndDate]))+
(((Date.Year([EndDate])-Date.Year([StartDate]))-1)*12)

Upvotes: 0

Mario LOBO
Mario LOBO

Reputation: 41

I use this (I calculate with half months). For full months use 30 and 1, for quarter months use 7.5 and 4.

Number.IntegerDivide(Duration.Days([End]-[Start]), 15)/2)

Upvotes: 1

JimmyWeb
JimmyWeb

Reputation: 103

As a new user to Power BI I am finding the need to filter between DAX and Power Query answers to be tiresome! DAX has a DATEDIFF function and Power Query (the M language?) doesn't? Why not?

There is a Duration function in M.

But that doesn't do months.

So I am grateful to you Thao N for asking and answering this question - a very neat line of code!

This is what you need:

((Date.Year([Change_Close_Date])-Date.Year([Change_Create_Date]))*12) + Date.Month([Change_Close_Date]) - Date.Month([Change_Create_Date])

Upvotes: 9

Related Questions