DATEDIF in Power Query

[Jon von der Heyden]

Hi

I am using Power Query (in Power BI Desktop), and I have a function to calculate difference between dates, in Years. I cannot find a DATEDIF equivalent, and battling to handle variable days in a year.

I have projected values with dates from 01-Jan-2023 through to 30 years from now, and can be attributed to any days on any months.

The base date to calculate the difference from (the effective date) will always be a quarter end date, being 31-Dec-YY; 31-Mar-YY, 30-Jun-YY, 30-Sep-YY.

When calculating the difference in Years, I need it to follow the same convention. E.g:

Effective (Reporting) Date: 30-Jun-2023
Projection date: 29-Jun-2024 Result: 0 Years
Projection date: 30-Jun-2024 Result: 1 Year
Projection date: 01-Jul-2024 Result: 1 Year

So effectively I believe I want to emulate DATEDIF in Excel.

I haven't figured out the code for this. Here is my initial rough attempt; which does not yield correct result because of different # days in years (leap years):

(EffectiveDate as date, ProjectionDate as date) =>
let
    A = Duration.Days(ProjectionDate - EffectiveDate),
    B = if A < 1 then -1 else A/365.25,
    C = B - Number.Mod(B,1)
in
    C

A little help please?

 

[Kerryx]

DATEDIF for POWER QUERY - Excel Exercise

This article gives you the M Code for Power Query to calculate the difference between 2 dates like the Excel function DATEDIF.

www.excel-exercise.com

 

[Jon von der Heyden]

Thanks Kerryx