Dax Function: YEARFRAC

Category: Date and Time Functions

The YEARFRAC function in Power BI is a DAX function used to calculate the fractional number of years between two dates. This function is useful for financial, academic, or project-based calculations where a fraction of the year is relevant.

Purpose

Computes the number of years (including fractional portions) between two dates.

Type of Calculations

Time-based calculations involving fractions of years.

Practical Use Cases

  • Calculating prorated metrics, such as depreciation or interest.
  • Determining time elapsed in financial contracts.
  • Calculating tenure in fractional years for employees or assets.

YEARFRAC(<start_date>, <end_date>, [basis])</end_date></start_date>
ParameterTypeDescription
start_dateScalarThe starting date for the calculation. Must be a valid date.
end_dateScalarThe ending date for the calculation. Must be a valid date.
[basis]Scalar(Optional) Specifies the day count basis to use. The default value is 0 (US 30/360 day count convention).

How Does YEARFRAC Dax Function Works

The YEARFRAC function calculates the fraction of the year between two dates by dividing the number of days between the dates by the total number of days in a year (determined by the day count basis).

Mathematically:

YEARFRAC = Days Between Dates / Days in Year

For example:

  • Using the Actual/Actual basis (basis = 1), the fraction is determined by the exact days in each year.
  • Using the 30/360 basis (basis = 0), every month is assumed to have 30 days, simplifying financial calculations.

What Does It Return?

  • Type: Decimal.
  • Meaning: A numeric value representing the fractional number of years between the two dates, calculated based on the specified day count basis.

When Should We Use It?

  • Financial Analysis: Calculate prorated interest, depreciation, or payments based on partial years.
  • Academic Applications: Measure semester or quarter durations as fractions of a year.
  • Tenure Calculations: Determine employee or asset tenure in fractional years.

Examples

Basic Usage

Calculate the fraction of the year between two dates:


YearFraction = YEARFRAC(DATE(2025, 1, 1), DATE(2025, 6, 30))

Output: 0.5 (Half of a year elapsed).

Column Usage:

Create a calculated column to calculate fractional years for multiple records:


TenureYears = YEARFRAC(Employees[StartDate], Employees[EndDate])

Output: Returns the tenure in years for each employee based on their start and end dates.

Advanced Usage

Combine with other DAX functions to create dynamic calculations:


YearFractionFiltered = CALCULATE(
SUM(Sales[Revenue]),
YEARFRAC(Sales[ContractStart], Sales[ContractEnd], 1) &gt; 0.75
)

Output: Summarizes sales revenue for contracts longer than 75% of a year.

Tips and Tricks

  • Select the Right Basis: Use the basis parameter carefully, especially in financial calculations where assumptions about day counts can significantly impact results.
  • Ensure Valid Date Formats: Inputs must be in valid date formats; otherwise, the function will return an error.
  • Combine with Conditional Logic: Use YEARFRAC with logical functions like IF or FILTER for more complex scenarios.

Potential Pitfalls

  • Default Basis: The default basis = 0 (US 30/360) may not align with your calculation needs.
  • Handling of Leap Years: Differences in handling leap years across bases can lead to unexpected results.

Performance Impact of YEARFRAC DAX Function:

  • Lightweight and efficient for scalar calculations.
  • For large datasets, ensure calculated columns or measures use optimized filters to avoid performance overhead.

Related Functions You Might Need

  • DATEDIFF: Calculates the difference between two dates in specified intervals (e.g., days, months, years).
  • YEAR: Extracts the year from a date.
  • MONTH: Extracts the month from a date.
  • DAY: Extracts the day from a date.
  • DATE: Constructs a date from year, month, and day values.

Want to Learn More?
For more information, check out the official Microsoft documentation for YEARFRAC You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the YEARFRAC function do in Power BI?

It calculates the fractional number of years between two dates based on a specified day count basis.

2. What is the default day count basis in YEARFRAC?

The default is 0 (US 30/360), assuming 30-day months and a 360-day year.

3. Can YEARFRAC handle leap years?

Yes, depending on the selected basis (1 for Actual/Actual).

4. What happens if one or both date inputs are invalid?

The function will return an error.

5. How do I calculate tenure in fractional years?

Use YEARFRAC(StartDate, EndDate) to determine the exact tenure in years.

Previous article
Dax Function: YEAR
Back to module