Daily DAX : Day 337 ENDOFYEAR

Power BI DAX: ENDOFYEAR Function

Description

The ENDOFYEAR function in Power BI DAX (Data Analysis Expressions) returns the last date of the year for a given date column. It is commonly used in time intelligence calculations to analyze data at the year-end level, such as financial reporting or annual summaries.

Syntax

ENDOFYEAR(<dates>[, <year_end_date>])

• <dates>: A column containing date values or an expression that returns a date.
• <year_end_date>: (Optional) A literal string (e.g., "12/31" or "30/06") specifying a custom year-end date. If omitted, it defaults to December 31.

Return Value

A single date representing the last day of the year for the specified date(s).

Use Case

The ENDOFYEAR function is useful for:

• Calculating year-end values, such as total sales or inventory levels at the end of the year.
• Comparing data points at the end of different years.
• Financial reporting where fiscal years may not align with the calendar year.

Example

Suppose you have a table Sales with a Date column and a Revenue column. You want to calculate the total revenue for the last day of the year.

YearEndRevenue = 
CALCULATE(
    SUM(Sales[Revenue]),
    ENDOFYEAR(Sales[Date])
)

This measure sums the Revenue for the last date of the year based on the Date column.

Example with Custom Year-End

For a fiscal year ending on June 30:

FiscalYearEndRevenue = 
CALCULATE(
    SUM(Sales[Revenue]),
    ENDOFYEAR(Sales[Date], "30/06")
)

This returns the total revenue for June 30 of the given year.

Notes

• The dates argument must reference a valid date column in your data model.
• Ensure a date table is marked as a Date Table in Power BI for accurate time intelligence calculations.
• If a custom year_end_date is provided, it applies to all dates processed by the function.