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
https://gist.github.com/ImkeF/d2a1ea92971a356054759a444cf0461b
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 😉
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
Hi, I encounter the same issue, have you been able to fix it? If yes can I have your solution? Thank u! 😀