DATEDIFF for POWER QUERY

DATEDIFF for POWER QUERY
Last Updated on 09/09/2023
Reading time: 3 minutes

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

    Collec 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

  3. Call your custom function

    Demo to use your custom DATEDIFF function

Copy each code in a different query

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

Menu create blank query

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

Paste the code in the Editor

The code of the DATEDIFF function

The following codes calculate the 5 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

To calculate the number of years, I have just applied the standard Power Query steps

  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 take into consideration 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

Full Power Query project

Application in Excel

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

Menu Invoke custom function

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

Call the function DATEDIF with the parameter Y

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

Result of the Power Query function in Excel

The difference with the Excel DATEDIF function

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

Difference with the Excel function DATEDIF for the parameter MD

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

DATEDIFF for POWER QUERY

Reading time: 3 minutes
Last Updated on 09/09/2023

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

    Collec 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

  3. Call your custom function

    Demo to use your custom DATEDIFF function

Copy each code in a different query

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

Menu create blank query

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

Paste the code in the Editor

The code of the DATEDIFF function

The following codes calculate the 5 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

To calculate the number of years, I have just applied the standard Power Query steps

  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 take into consideration 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

Full Power Query project

Application in Excel

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

Menu Invoke custom function

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

Call the function DATEDIF with the parameter Y

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

Result of the Power Query function in Excel

The difference with the Excel DATEDIF function

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

Difference with the Excel function DATEDIF for the parameter MD

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.

Leave a Reply

Your email address will not be published. Required fields are marked *