Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to use the YEARFRAC DAX Function. Let’s get started
YEARFRAC DAX function calculates and returns the fraction of the year represented by the number of whole days between two dates. YEARFRAC DAX function uses datetime format in lieu of the serial numbers that is used in YEARFRAC in Excel
YEARFRAC Syntax =YEARFRAC(start_date, end_date, [basis])
In the caption below, we want to create a calculated column to calculate how many years each of the employee have been working with us up to October 30, 2019
In the Calculations group of the Modelling tab, click on New Column
In the formula bar, execute the following:
- Years in Service = YEARFRAC(EmployeeData[Date of Hire],TODAY()-19)
- Years of Service represent the column name
- EmployeeData is the table name
- [Date of Hire] represent column in the EmployeeData table
Since we want to calculate how many years and month each of the employee has been working with us up to 30th November 2019, we used TODAY DAX function and subtract 19 days. Note, this exercise was performed on November 19, 2019. The minus 19 moves us 19 days before
In the caption above, the first employee has been working with the company for 14 years and nearly 3 months