Daily DAX : Day 320 ENDOFQUARTER

Power BI DAX ENDOFQUARTER Function

Description

The ENDOFQUARTER function in Power BI's Data Analysis Expressions (DAX) returns the last date of the quarter for a given date. It is a time intelligence function that helps in analyzing data based on quarterly periods.

Syntax

ENDOFQUARTER(<dates>)

• <dates>: A column containing date values or an expression that returns a single date.

Return Value

A date representing the last day of the quarter for the input date(s).

Use Case

The ENDOFQUARTER function is used in scenarios where you need to aggregate or filter data up to the end of a quarter, such as:

• Calculating total sales or revenue for a quarter.
• Comparing performance metrics at the end of each quarter.
• Creating time-based calculations, like quarter-end inventory levels.

Example

Suppose you have a table named Sales with a Date column. You want to create a measure to calculate total sales up to the end of the quarter for any given date.

QuarterEndSales = 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(
        Sales,
        Sales[Date] <= ENDOFQUARTER(Sales[Date])
    )
)

In this example, the measure QuarterEndSales calculates the sum of sales amounts from the start of the quarter to the last date of the quarter for the dates in the Sales[Date] column.

Notes

• ENDOFQUARTER requires a date column in a valid datetime format.
• It assumes a standard calendar where quarters end on March 31, June 30, September 30, and December 31.
• Ensure a date table is marked as a date table in Power BI for accurate time intelligence calculations.