YEARFRAC DAX Function (Date and Time)

Syntax  |  Return values  |  Remarks  |  Examples  |  Articles  |  Related  

Returns the year fraction representing the number of whole days between start_date and end_date.

Syntax

YEARFRAC ( <StartDate>, <EndDate> [, <Basis>] )

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. 0 (default) : US (NASD) 30/360 1 : Actual/actual 2 : Actual/360 3 : Actual/365 4 : European 30/360

Return values

Scalar A single decimal value.

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.

» 1 related article
» 3 related functions

Examples

1 2 3 4 5 6 7 8 9 10 11 12 13 14

--  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 ) )     }

 Try it Copy#1

Value1	Value2
DATEDIFF Year	1.00
DATEDIFF Quarter	7.00
DATEDIFF Month	23.00
DATEDIFF Day	714.00
Subtraction	714.00
YEARFRAC	1.96

1 2 3 4 5 6 7 8 9 10

--  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 )     }

 Try it Copy#2

Value1	Value2
YEARFRAC	1.96
Number of days	713.00
YEARFRAC *365	713.78

1 2 3 4 5 6 7 8 9 10 11 12 13 14

--  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" ) )     }

 Try it Copy#3

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:

• WEEKDAY
• WEEKNUM
• QUARTER

Last update: Apr 15, 2025   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

Microsoft documentation: https://docs.microsoft.com/en-us/dax/yearfrac-function-dax