Daily DAX : Day 429 DATESINPERIOD

Power BI DAX Function: DATESINPERIOD

Description

The DATESINPERIOD function is a Time Intelligence function in DAX that returns a table containing a single column of dates. These dates begin from a specified start date and continue for a given number of intervals (forward or backward in time).

It is commonly used with the CALCULATE function to modify the filter context for time-based calculations, such as rolling totals or moving averages.

Syntax

DATESINPERIOD(, , , )
  • : A reference to a date column (usually from a marked Date table).
  • : The starting date for the period (often MAX('Date'[Date]) for dynamic periods).
  • : The number of intervals to include. Positive for future, negative for past.
  • : The type of interval: DAY, MONTH, QUARTER, or YEAR (no quotes needed).

Use Cases

DATESINPERIOD is ideal for rolling or trailing periods where the end date is dynamic (e.g., the last available date in the current filter context).

  • Calculating sales for the last 12 months (Moving Annual Total).
  • Rolling 3-month averages.
  • Trailing 30-day totals.
  • Year-to-date comparisons excluding the current partial period.

It differs from DATESBETWEEN, which requires both a fixed start and end date.

Examples

1. Last 12 Months Sales (Moving Annual Total)

Sales Last 12 Months = CALCULATE(
    [Total Sales],
    DATESINPERIOD(
        'Date'[Date],
        MAX('Date'[Date]),
        -12,
        MONTH
    )
)

This calculates the total sales from the current date going back 12 months.

2. Last Year Sales (Trailing 1 Year)

Sales Last Year = CALCULATE(
    [Total Sales],
    DATESINPERIOD(
        'Date'[Date],
        MAX('Date'[Date]),
        -1,
        YEAR
    )
)

Returns sales for the previous 365/366 days ending on the latest date in context.

3. Rolling 30-Day Sales

Sales Last 30 Days = CALCULATE(
    [Total Sales],
    DATESINPERIOD(
        'Date'[Date],
        MAX('Date'[Date]),
        -30,
        DAY
    )
)

Useful for daily rolling metrics.

Note: Always use a proper Date table marked as such in Power BI for best results with Time Intelligence functions.

Comments

Post a Comment

Popular posts from this blog

Daily DAX : Day 131 SELECTEDMEASURE

 The SELECTEDMEASURE function in Power BI's DAX (Data Analysis Expressions) language is a powerful tool used primarily within calculation groups to dynamically reference the measure currently being evaluated in a report. It’s part of the calculation group feature introduced to streamline complex reporting scenarios and improve maintainability by reducing the need to duplicate measure logic. What is SELECTEDMEASURE? SELECTEDMEASURE is a DAX function that returns a reference to the measure that is currently active or "selected" in the context of a calculation group. It doesn’t take any arguments and is typically used in the expression of a calculation item within a calculation group. This allows you to write generic logic that can dynamically apply to whichever measure is being evaluated in a visual, without hardcoding specific measure names. Syntax SELECTEDMEASURE()     Returns: A reference to the measure currently being evaluated in the report context. Use Case SELECTEDME...
Read more

Daily DAX : Day 446 INFO.CSDLMETADATA

Power BI DAX Function: INFO.CSDLMETADATA INFO.CSDLMETADATA` is a specialized DAX function introduced as part of a broader set of "INFO" functions (like `INFO.TABLES` or `INFO.MEASURES`) used for **model introspection**. It allows you to programmatically access the underlying structure of your Power BI semantic model. What does it do? The function returns a table with a single column containing the Conceptual Schema Definition Language (CSDL)  metadata of your model in XML format . In plain English, it exports a highly technical "blueprint" of your entire data model—including tables, columns, relationships, and measures—as a structured XML string. It is essentially the DAX equivalent of the `DISCOVER_CSDL_METADATA` Dynamic Management View (DMV) used in Analysis Services. --- Syntax and Output The syntax is straightforward as it requires no arguments: ```dax EVALUATE INFO.CSDLMETADATA() ``` Return Value : A table with one column and one row. Content : A long string ...
Read more

Daily DAX : Day 453 ENDOFWEEK

Power BI DAX Function: ENDOFWEEK Description The ENDOFWEEK function is a Time Intelligence function in DAX (Data Analysis Expressions) used in Power BI. It returns the date that represents the end of the week containing a specified date (typically Saturday, as weeks are considered to start on Sunday by default in DAX time intelligence). Syntax ENDOFWEEK( [, ]) : A column reference (usually from a Date table) or a date expression. Required. : Optional. Specifies the week-ending convention. Default is Saturday if omitted. Examples 1. Basic usage (default: week ends on Saturday): End of Week = ENDOFWEEK('Date'[Date]) For a date like January 8, 2026 (Thursday), this would return January 10, 2026 (Saturday). 2. In a measure for cumulative sales up to end of week: Total Sales to End of Week = CALCULATE( SUM(Sales[Amount]), DATESBETWEEN('Date'[Date], ST...
Read more