YEARFRAC DAX Function (Date and Time)
Returns the year fraction representing the number of whole days between start_date and end_date.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| StartDate |
The start date in datetime format. |
|
| EndDate |
The end date in datetime format. |
|
| Basis | Optional |
The type of day count basis to use. |
Return values
Fraction of the year.
Remarks
If the argument is a string, it is translated into a DateTime value using the same rules applied by the DATEVALUE function.
YEARFRAC can be used to compute the current age of a customer based on the difference between the current day and the customer’s birthdate, but because of a bug, it is suggested to use another technique (also faster) based on quotient and floor, as described in related content.
» 3 related functions
Examples
-- DATEDIFF computes the delta between two dates, using different units of measure
-- YEAFRAC returns the delta as a fraction (in years)
EVALUATE
VAR StartDate = DATE ( 2011, 01, 01 )
VAR EndDate = DATE ( 2012, 12, 15 )
RETURN
{
( "DATEDIFF Year", DATEDIFF ( StartDate, EndDate, YEAR ) ),
( "DATEDIFF Quarter", DATEDIFF ( StartDate, EndDate, QUARTER ) ),
( "DATEDIFF Month", DATEDIFF ( StartDate, EndDate, MONTH ) ),
( "DATEDIFF Day", DATEDIFF ( StartDate, EndDate, DAY ) ),
( "Subtraction", INT ( EndDate - StartDate ) ),
( "YEARFRAC", YEARFRAC ( StartDate, EndDate ) )
}
| Value1 | Value2 |
|---|---|
| DATEDIFF Year | 1.00 |
| DATEDIFF Quarter | 7.00 |
| DATEDIFF Month | 23.00 |
| DATEDIFF Day | 714.00 |
| Subtraction | 714.00 |
| YEARFRAC | 1.96 |
-- The default of YEARFRAC is "US 30/360"
EVALUATE
VAR StartDate = DATE ( 2010, 01, 01 )
VAR EndDate = DATE ( 2011, 12, 15 )
RETURN
{
( "YEARFRAC", YEARFRAC ( StartDate, EndDate ) ),
( "Number of days", INT ( EndDate - StartDate ) ),
( "YEARFRAC *365", YEARFRAC ( StartDate, EndDate ) * 365 )
}
| Value1 | Value2 |
|---|---|
| YEARFRAC | 1.96 |
| Number of days | 713.00 |
| YEARFRAC *365 | 713.78 |
-- Different standards produce different fractions
-- YEAFRAC is intended as a financial function, following the required
-- standard of 30/360.
EVALUATE
VAR StartDate = DATE ( 2011, 01, 01 )
VAR EndDate = DATE ( 2011, 12, 15 )
RETURN
{
( "YEARFRAC US 30/360", FORMAT ( YEARFRAC ( StartDate, EndDate, 0 ), "0.0000" ) ),
( "YEARFRAC Actual / Actual", FORMAT ( YEARFRAC ( StartDate, EndDate, 1 ), "0.0000" ) ),
( "YEARFRAC Actual / 360", FORMAT ( YEARFRAC ( StartDate, EndDate, 2 ), "0.0000" ) ),
( "YEARFRAC Actual / 365", FORMAT ( YEARFRAC ( StartDate, EndDate, 3 ), "0.0000" ) ),
( "YEARFRAC EU 30/360", FORMAT ( YEARFRAC ( StartDate, EndDate, 4 ), "0.0000" ) )
}
| Value1 | Value2 |
|---|---|
| YEARFRAC US 30/360 | 0.9556 |
| YEARFRAC Actual / Actual | 0.9534 |
| YEARFRAC Actual / 360 | 0.9667 |
| YEARFRAC Actual / 365 | 0.9534 |
| YEARFRAC EU 30/360 | 0.9556 |
Related articles
Learn more about YEARFRAC in the following articles:
-
Correct calculate of age in DAX from birthday
Consider alternative to YEARFRAC in order to get the right age based on birthday because of bugs in YEARFRAC. » Read more
Related functions
Other related functions are:
Last update: Nov 28, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/yearfrac-function-dax