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: Dec 4, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/yearfrac-function-dax