DAX Function for 12-Month Occurrence Detection

This document outlines a DAX function designed to detect occurrences of a specific event over any rolling 12-month period. This is commonly useful in reporting and analysis scenarios where trend over time is essential.

Function Overview

Purpose

To calculate the number of occurrences of an event within the last 12 months from a given date context.

Parameters

• Event Table: A table containing event records.
• Date Column: The date column in the event table used for occurrence analysis.

Return Type

• Returns a count of occurrences within the specified 12-month rolling window.

Exceptions

• Will return 0 if the input data is empty or invalid.

Implementation

Code

CountOccurrencesLast12Months = 
VAR CurrentDate = MAX('Date'[Date])   // Get the latest date in context
VAR StartDate = EDATE(CurrentDate, -12) // Calculate the start of the 12-month period
RETURN
    CALCULATE(
        COUNTROWS(EventTable), // Count occurrences in EventTable
        EventTable[Date] >= StartDate && EventTable[Date] <= CurrentDate // Filter for the last 12 months
    )

Explanation of the Code

• CurrentDate: Grabs the maximum date in the current filter context (latest date).
• StartDate: Computes the date 12 months prior to CurrentDate.
• CALCULATE: Adjusts the context in which the data is evaluated.
• COUNTROWS: Counts the number of rows in the EventTable that satisfy the date filter condition.

Input Validation

• The function assumes that the EventTable and Date columns are valid and properly formatted.
• It will gracefully handle cases where there are no events, returning 0.

Example Usage

Scenario

In a sales dashboard, you want to analyze the number of sales made in the last 12 months for each customer.

DAX Measure Implementation

SalesLast12Months = 
CountOccurrencesLast12Months

Result Interpretation

Once implemented, this measure can be used in charts or tables to visualize sales trends for customers dynamically, showing how sales have fluctuated over the past year.

Conclusion

This DAX function efficiently analyzes occurrences over a rolling 12-month period, providing valuable insights into event trends. For further learning, consider exploring courses on the Enterprise DNA Platform to enhance your DAX capabilities.