Power BI: YEARFRAC DAX Function

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

Posted in Blog and tagged , , .

Leave a Reply

Your email address will not be published. Required fields are marked *