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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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)) |
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 😉
Pingback: YEARFRAC() in Power Query – Curated SQL
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