-
-
Save ImkeF/d2a1ea92971a356054759a444cf0461b to your computer and use it in GitHub Desktop.
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.
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)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment