YEARFRAC
The fraction of a year between two dates, by a day-count basis (US 30/360 by default).
Group: Date and time. Compatible with DAX.
Syntax
YEARFRAC ( <start_date>, <end_date> [, <basis>] )| Parameter | Type | Description |
|---|---|---|
start_date | Date | One end of the period. |
end_date | Date | The other end. |
basis | Whole number optional | How days are counted: 0 US 30/360 (when left out), 1 actual days over the actual year, 2 actual days over 360, 3 actual days over 365, 4 European 30/360. |
Returns
Number. The part of a year between the two dates, never negative.
Remarks
- The two dates may come in either order, and their times of day play no part.
- The 30/360 bases count every month as 30 days, and differ at month ends: from 15 to 31 March is 16 days by the US count and 15 by the European one.
- A blank date is taken as 30 December 1899; with both blank the result is BLANK.
- A decimal basis is rounded; one outside 0 to 4 is an error.
- Typical uses: ages, the time left to a bond's maturity, a year's share of an interest payment.
Examples
The five bases over half a year
EVALUATE
VAR A = DATE ( 2024, 1, 31 )
VAR B = DATE ( 2024, 7, 31 )
RETURN
ROW (
"0", YEARFRAC ( A, B ), "1", YEARFRAC ( A, B, 1 ), "2", YEARFRAC ( A, B, 2 ),
"3", YEARFRAC ( A, B, 3 ), "4", YEARFRAC ( A, B, 4 )
)| 0 | 1 | 2 | 3 | 4 |
|---|---|---|---|---|
| 0.5 | 0.497267759562842 | 0.505555555555556 | 0.498630136986301 | 0.5 |
Years from New Year 2026 to each maturity
EVALUATE
SELECTCOLUMNS (
Holdings,
"Bond", Holdings[Bond],
"Maturity", Holdings[Maturity],
"Years left", ROUND ( YEARFRAC ( DATE ( 2026, 1, 1 ), Holdings[Maturity], 1 ), 2 )
)
ORDER BY [Maturity]| Bond | Maturity | Years left |
|---|---|---|
| Government 2028 | 2028-03-02 | 2.17 |
| Covered 2029 | 2029-06-20 | 3.47 |
| Corporate 2030 | 2030-11-25 | 4.9 |
| Municipal 2031 | 2031-09-01 | 5.67 |
| Government 2033 | 2033-03-15 | 7.2 |