# DATEDIFF for POWER QUERY

Last Updated on 12/05/2024

How to perform a DATEDIFF for POWER QUERY? The code to build your custom Power Query function is in this article

1. The code for each calculation

Collect the code for each calculation returns by the DATEDIFF function

2. Write the code into a custom query

Create custom queries to copy your code

Demo to use your custom DATEDIFF function

## Copy each code in a different query

For each following code, you must write them in a Blank query (Get Data > From Other Source > Blank Query)

And you will paste the codes in the Power Query editor (Home > Advanced Editor)

## The DATEDIFF function code for Power Query

The following codes return the five options of the DATEDIFF function.

### Function fn_D

This is the easiest parameter to build

``````(date1, date2) =>
let
Source = Duration.Days(date2 - date1)
in
Source``````

### Function fn_Y

I have just applied the standard Power Query steps to calculate the number of years.

1. Difference between the 2 dates in days
2. Convert the type of the column in Duration
3. Return the duration in Years
``````(date1, date2) =>
let
Source = Duration.Days(date2 - date1),
TheDuration = (Duration.TotalDays(Duration.From(Source)) / 365),
YearResult = Number.RoundDown(TheDuration)
in
YearResult``````

### Function fn_YM

Then, we calculate the number of months in the current year (parameter YM). Here, we must consider if the day and the month are greater or lower between the 2 dates.

``````(date1, date2)=>
let
GapDay = Date.Day(date2) - Date.Day(date1),
GapMonth = Date.Month(date2) - Date.Month(date1),
NbMonth = if GapDay < 0 then GapMonth - 1 else GapMonth,
YM = if NbMonth < 0 then (12 + NbMonth) else NbMonth
in
YM``````

### Function fn_MD

Here, I return the number of days in the current month (MD) and there is a difference with the DATEDIF function of Excel.

The logic here is to 'build' a date with the last day of the month of the date1. If the first day is lower than the second day, I simply subtract the 2 values. But if Day1 is greater, then I calculate the difference between the last day of the month and date1 and finally add the number of Day2

``````(date1, date2)=>
let
Day1 = Date.Day(date1),
Day2 = Date.Day(date2),
DayEndMonth = Date.Day(Date.EndOfMonth(date1)),
MD = if Day1 <= Day2 then Day2 - Day1 else (DayEndMonth-Day1)+Day2
in
MD``````

### Function fn_M

To calculate the number of months between the 2 dates, I need to call the function fn_Y and multiply by 12, and then, add the result of the function fn_YM.

``````(date1, date2) =>
let
Step1 = fn_Y(date1, date2)*12,
Step2 = Step1 + fn_YM(date1, date2)
in
Step2``````

### Function fn_YD

Here again, there could be a difference with the function DATEDIF but this parameter is practically never used (I used it once in 20 years). Anyway, the logic here is to build a date with the year of date2 and the day and the month of date1. If the new date is greater than date2, I rebuild a date for the previous year. Finally, I just need to subtract the 2 dates to return the number of days in the year.

``````(date1, date2)=>
let
Year2 = Date.Year(date2),
BuildDate = #date(Year2, Date.Month(date1), Date.Day(date1)),
TestNewDate = if BuildDate < date2
then BuildDate
else #date(Year2 - 1, Date.Month(date1), Date.Day(date1)),
YD = Duration.Days(date2 - TestNewDate)
in
YD``````

## DATEDIF for POWER QUERY

Finally, I include all these functions inside another Power Query function called fn_DateDif

``````(date1, date2, ParamDate)=>
let
ValParam = Text.Upper(ParamDate),
ResultFunction = if ValParam = "D" then fn_D(date1, date2)
else if ValParam = "Y" then fn_Y(date1, date2)
else if ValParam = "M" then fn_M(date1, date2)
else if ValParam = "YM" then fn_YM(date1, date2)
else if ValParam = "MD" then fn_MD(date1, date2)
else if ValParam = "YD" then fn_YD(date1, date2)
else "Wrong parameter"
in
ResultFunction``````

When you have copied all the codes, you have this

## Application in Excel

Now in Power Query, you need to call the function with the menu Add Column > Invoke Custom Function.

Then, you call the fn_DateDif function and fill in the 3 parameters like in this example.

And when you load the data in Excel, you have this result

## The difference with the Excel DATEDIF function

When you test the Power Query function with the Excel function, the parameter MD will have different results.

It's because the logic to calculate this parameter isn't the same for Excel. For the first example, Excel adds 12 years and 3 months to the first date. So the 'new date' is 22/05/2017. And in this situation, 09/06/2017 - 22/05/2017 = 18 days. In fact, the difference could be explained by the number of leap years.

# DATEDIFF for POWER QUERY

Last Updated on 12/05/2024

How to perform a DATEDIFF for POWER QUERY? The code to build your custom Power Query function is in this article

1. The code for each calculation

Collect the code for each calculation returns by the DATEDIFF function

2. Write the code into a custom query

Create custom queries to copy your code

Demo to use your custom DATEDIFF function

## Copy each code in a different query

For each following code, you must write them in a Blank query (Get Data > From Other Source > Blank Query)

And you will paste the codes in the Power Query editor (Home > Advanced Editor)

## The DATEDIFF function code for Power Query

The following codes return the five options of the DATEDIFF function.

### Function fn_D

This is the easiest parameter to build

``````(date1, date2) =>
let
Source = Duration.Days(date2 - date1)
in
Source``````

### Function fn_Y

I have just applied the standard Power Query steps to calculate the number of years.

1. Difference between the 2 dates in days
2. Convert the type of the column in Duration
3. Return the duration in Years
``````(date1, date2) =>
let
Source = Duration.Days(date2 - date1),
TheDuration = (Duration.TotalDays(Duration.From(Source)) / 365),
YearResult = Number.RoundDown(TheDuration)
in
YearResult``````

### Function fn_YM

Then, we calculate the number of months in the current year (parameter YM). Here, we must consider if the day and the month are greater or lower between the 2 dates.

``````(date1, date2)=>
let
GapDay = Date.Day(date2) - Date.Day(date1),
GapMonth = Date.Month(date2) - Date.Month(date1),
NbMonth = if GapDay < 0 then GapMonth - 1 else GapMonth,
YM = if NbMonth < 0 then (12 + NbMonth) else NbMonth
in
YM``````

### Function fn_MD

Here, I return the number of days in the current month (MD) and there is a difference with the DATEDIF function of Excel.

The logic here is to 'build' a date with the last day of the month of the date1. If the first day is lower than the second day, I simply subtract the 2 values. But if Day1 is greater, then I calculate the difference between the last day of the month and date1 and finally add the number of Day2

``````(date1, date2)=>
let
Day1 = Date.Day(date1),
Day2 = Date.Day(date2),
DayEndMonth = Date.Day(Date.EndOfMonth(date1)),
MD = if Day1 <= Day2 then Day2 - Day1 else (DayEndMonth-Day1)+Day2
in
MD``````

### Function fn_M

To calculate the number of months between the 2 dates, I need to call the function fn_Y and multiply by 12, and then, add the result of the function fn_YM.

``````(date1, date2) =>
let
Step1 = fn_Y(date1, date2)*12,
Step2 = Step1 + fn_YM(date1, date2)
in
Step2``````

### Function fn_YD

Here again, there could be a difference with the function DATEDIF but this parameter is practically never used (I used it once in 20 years). Anyway, the logic here is to build a date with the year of date2 and the day and the month of date1. If the new date is greater than date2, I rebuild a date for the previous year. Finally, I just need to subtract the 2 dates to return the number of days in the year.

``````(date1, date2)=>
let
Year2 = Date.Year(date2),
BuildDate = #date(Year2, Date.Month(date1), Date.Day(date1)),
TestNewDate = if BuildDate < date2
then BuildDate
else #date(Year2 - 1, Date.Month(date1), Date.Day(date1)),
YD = Duration.Days(date2 - TestNewDate)
in
YD``````

## DATEDIF for POWER QUERY

Finally, I include all these functions inside another Power Query function called fn_DateDif

``````(date1, date2, ParamDate)=>
let
ValParam = Text.Upper(ParamDate),
ResultFunction = if ValParam = "D" then fn_D(date1, date2)
else if ValParam = "Y" then fn_Y(date1, date2)
else if ValParam = "M" then fn_M(date1, date2)
else if ValParam = "YM" then fn_YM(date1, date2)
else if ValParam = "MD" then fn_MD(date1, date2)
else if ValParam = "YD" then fn_YD(date1, date2)
else "Wrong parameter"
in
ResultFunction``````

When you have copied all the codes, you have this

## Application in Excel

Now in Power Query, you need to call the function with the menu Add Column > Invoke Custom Function.

Then, you call the fn_DateDif function and fill in the 3 parameters like in this example.

And when you load the data in Excel, you have this result

## The difference with the Excel DATEDIF function

When you test the Power Query function with the Excel function, the parameter MD will have different results.

It's because the logic to calculate this parameter isn't the same for Excel. For the first example, Excel adds 12 years and 3 months to the first date. So the 'new date' is 22/05/2017. And in this situation, 09/06/2017 - 22/05/2017 = 18 days. In fact, the difference could be explained by the number of leap years.