Excel YEARFRAC function for Power Query

While there is a native YEARFRAC-function in DAX that you can use to calculate year fractions, sometimes you might just need it in Power Query. The function I’m presenting here has the same function arguments than its Excel-equivalent. Its 5 different modes require some advanced calculations. Fortunately I found a great resource on how to calculate it here. So my version here is basically a direct translation into the M-language.

The code for the Excel YEARFRAC function for Power Query


let func =
let
//Algo source: https://lists.oasis-open.org/archives/office-formula/200806/msg00039.html
FIsLeapYear = (Year) =>
if Number.Mod(Year, 4) > 0 then
false
else if Number.Mod(Year, 100) > 0 then
true
else if Number.Mod(Year, 400) = 0 then
true
else
false,
FIsEndOfMonth = (Day, Month, Year) =>
if List.Contains({1, 3, 5, 7, 8, 10, 12}, Day) then
31
else if List.Contains({4, 6, 9, 11}, Day) then
30
else if FIsLeapYear(Year) then
29
else
28,
Days360 = (StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay) =>
((EndYear – StartYear) * 360) + ((EndMonth – StartMonth) * 30) + (EndDay – StartDay),
TmpDays360Nasd = (StartDate, EndDate, Method, UseEom) =>
let
StartDay = Date.Day(StartDate),
StartMonth = Date.Month(StartDate),
StartYear = Date.Year(StartDate),
EndDay = Date.Day(EndDate),
EndMonth = Date.Month(EndDate),
EndYear = Date.Year(EndDate),
Select = [
EndDay =
if (EndMonth = 2 and FIsEndOfMonth(EndDay, EndMonth, EndYear))
and (
(StartMonth = 2 and FIsEndOfMonth(StartDay, StartMonth, StartYear))
or Method
= 3
)
then
30
else if EndDay = 31 and (StartDay >= 30 or Method = 3) then
30
else
EndDay,
StartDay =
if StartDay = 31 then
30
else if (
UseEom
= 2 and StartMonth
= 2 and FIsEndOfMonth(StartDay, StartMonth, StartYear)
)
then
30
else
StartDay
],
TmpDays360Nasd = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay, EndDay)
in
#"TmpDays360Nasd",
TmpDays360Euro = (StartDate, EndDate) =>
let
StartDay = Date.Day(StartDate),
StartMonth = Date.Month(StartDate),
StartYear = Date.Year(StartDate),
EndDay = Date.Day(EndDate),
EndMonth = Date.Month(EndDate),
EndYear = Date.Year(EndDate),
StartDay_ = if (StartDay = 31) then 30 else StartDay,
EndDay_ = if (EndDay = 31) then 30 else EndDay,
TmpDays360Euro = Days360(StartYear, EndYear, StartMonth, EndMonth, StartDay_, EndDay_)
in
TmpDays360Euro,
TmpDiffDates = (StartDate, EndDate, Basis) =>
if Basis = 0 then
TmpDays360Nasd(StartDate, EndDate, 0, true)
else if List.Contains({1, 2, 3}, Basis) then
Duration.Days(EndDate – StartDate)
else
TmpDays360Euro(StartDate, EndDate),
TmpCalcAnnualBasis = (StartDate, EndDate, Basis) =>
if List.Contains({0, 2, 4}, Basis) then
360
else if Basis = 3 then
365
else
let
StartDay = Date.Day(StartDate),
StartMonth = Date.Month(StartDate),
StartYear = Date.Year(StartDate),
EndDay = Date.Day(EndDate),
EndMonth = Date.Month(EndDate),
EndYear = Date.Year(EndDate),
TmpCalcAnnualBasis_ =
if (StartYear = EndYear) then
if FIsLeapYear(StartYear) then 366 else 365
else if ((EndYear – 1) = StartYear)
and (
(StartMonth > EndMonth)
or ((StartMonth = EndMonth) and StartDay >= EndDay)
)
then
if FIsLeapYear(StartYear) then
if StartMonth < 2 or (StartMonth = 2 and StartDay <= 29) then
366
else
365
else if FIsLeapYear(EndYear) then
if EndMonth > 2 or (EndMonth = 2 and EndDay = 29) then 366 else 365
else
365
else
List.Accumulate(
{StartYear .. EndYear},
0,
(state, current) =>
if FIsLeapYear(current) then state + 366 else state + 365
),
TmpCalcAnnualBasis__ = TmpCalcAnnualBasis_ / (EndYear – StartYear + 1)
in
TmpCalcAnnualBasis__,
Result = (StartDate, EndDate, Basis_) =>
let
Basis = if Basis_ = null then 0 else Basis_,
nNumerator = TmpDiffDates(StartDate, EndDate, Basis),
nDenom = TmpCalcAnnualBasis(StartDate, EndDate, Basis),
TmpYearFrac = nNumerator / nDenom
in
TmpYearFrac
in
Result ,
documentation = [
Documentation.Name = " Xls.YEARFRAC.pq ",
Documentation.Description = " Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). Equivalent of the YEARFRAC-Function in Excel. ",
Documentation.LongDescription = " Calculates the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date). For instance, you can use YEARFRAC to identify the proportion of a whole year's benefits, or obligations to assign to a specific term. Equivalent of the YEARFRAC-Function in Excel. ",
Documentation.Category = " Xls.Date ",
Documentation.Source = " www.TheBIcountant.com – https://wp.me/p6lgsG-2t4 . ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " let
Start_date = #date(2012,1,1) ,
End_date = #date(2012, 7, 30),
Basis = 3,
FunctionCall = Xls_YEARFRAC(Start_date, End_date, Basis)
in
FunctionCall ",
Result = " 0.578082191780821
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

Xls.YEARFRAC.pq

hosted with ❤ by GitHub

Function parameters

The Excel YEARFRAC function for Power Query has the same parameters than its Excel equivalent:

YEARFRAC(start_date, end_date, [basis])

The YEARFRAC function syntax has the following arguments:

  • Start_date    Required. A date that represents the start date.
  • End_date    Required. A date that represents the end date.
  • Basis    Optional. The type of day count basis to use.
Basis Day count basis
0 or omitted US (NASD) 30/360
1 Actual/actual
2 Actual/360
3 Actual/365
4

European 30/360

If you are interested in more Power Query functions that replicate Excel functions who haven’t made it into the M-language (yet?) please check out this collection.

Enjoy and stay queryious 😉

Comments (2) Write a comment

  1. Pingback: YEARFRAC() in Power Query – Curated SQL

  2. Hello

    The actual/actual is incorrect if less than 1 year. For example:

    3/10/2023 to 31/05/2024 the actual/actual is 0.65 (rounded to 2 decimal places), but using this custom function in Power Query it’s giving me 1.3.

    Any chance on correcting this?

    Thanks

    Reply

Leave a Reply