Daily DAX : Day 194 YEARFRAC

 The YEARFRAC function in Power BI DAX calculates the fraction of a year between two dates, based on a specified day count convention. It’s useful for determining the time duration in years, often for financial calculations, age calculations, or prorated metrics.

Syntax

DAX

YEARFRAC(start_date, end_date, [basis])

    start_date: The start date (a valid date expression).

    end_date: The end date (a valid date expression).

    basis (optional): An integer specifying the day count convention:

        0 or omitted: US (NASD) 30/360 (default).

        1: Actual/actual (uses the actual number of days in each month and year).

        2: Actual/360 (assumes a 360-day year).

        3: Actual/365 (assumes a 365-day year).

        4: European 30/360.

Return Value

A decimal number representing the fraction of a year between the two dates.

Use Cases

    Financial Calculations: Calculate interest or depreciation for a partial year.

        Example: Prorating annual interest for a loan based on the exact time period.

    Age Calculation: Determine a person’s or asset’s age in fractional years.

        Example: Calculating an employee’s tenure for benefits eligibility.

    Prorated Metrics: Allocate costs, revenues, or budgets based on partial-year periods.

        Example: Distributing an annual budget across months or quarters.

    Time-Based Analysis: Measure durations for project timelines or contract periods.

Example

Suppose you want to calculate the fraction of a year between January 1, 2024, and April 24, 2025, using the actual/actual basis.

DAX

FractionOfYear = YEARFRAC(DATE(2024, 1, 1), DATE(2025, 4, 24), 1)

Result: Approximately 1.31 (since 2024 is a leap year, and the period spans 479 days ÷ 366 days for 2024 plus part of 2025).

Notes

    Ensure valid date inputs to avoid errors.

    The choice of basis affects accuracy, especially for leap years or irregular periods.

    Commonly used in financial models, HR analytics, or time-series reporting.