DAX Function for Rolling 12-Month Task Instances

This section outlines a DAX function designed to retrieve instances of tasks occurring after a specified reference date during a rolling 12-month period.

Function Overview

Purpose

To return the count of tasks that occurred in the 12 months leading up to and including a provided reference date.

Parameters

• TaskTable: The table containing task data (e.g., with columns TaskID, TaskDate, etc.).
• ReferenceDate: The date from which to calculate the 12-month period, can be a date column from the TaskTable.

Return Type

• Returns an integer representing the count of tasks within the specified period.

Code Implementation

RollingTaskCount = 
VAR RefDate = MAX(TaskTable[TaskDate]) -- Determines the reference date to use
VAR StartDate = EDATE(RefDate, -12) -- Computes the start date for the 12-month period

RETURN 
    CALCULATE(
        COUNTROWS(TaskTable), -- Counts the number of tasks
        TaskTable[TaskDate] > RefDate, -- Filters tasks that happened after the reference date
        TaskTable[TaskDate] >= StartDate && TaskTable[TaskDate] <= RefDate -- Includes tasks within the rolling 12-month period
    )

Explanation of the Code

1. Reference Date Calculation: MAX(TaskTable[TaskDate]) retrieves the latest date in the task table, assuming it's used in a visual context where a specific date is being evaluated.

2. Start Date Calculation: EDATE(RefDate, -12) computes the date exactly 12 months prior to the reference date.

3. Task Count Calculation:

   • COUNTROWS(TaskTable) counts the total rows that match the criteria.
   • The filter conditions:
     • TaskTable[TaskDate] > RefDate ensures only tasks occurring after the reference date are counted.
     • TaskTable[TaskDate] >= StartDate && TaskTable[TaskDate] <= RefDate restricts the task dates to the rolling 12 months.

Input Validation

• Valid Date: Ensure that ReferenceDate provided is a valid date.
• Non-empty Table: Ensure that TaskTable is not empty to avoid errors during calculations.

Usage Example

This example demonstrates how to implement the function in a real-world scenario using a Power BI report where TaskTable represents the tasks associated with various dates.

Assuming a report where users select a specific date, the DAX formula can be used as follows:

TotalTasksAfterReferenceDate = RollingTaskCount

• You would place TotalTasksAfterReferenceDate in a visual chart (such as a card or table) to display the count of tasks that occurred in the last rolling 12 months from the selected date.

Conclusion

The provided DAX function effectively counts the number of tasks that occurred after a specified reference date in a 12-month rolling window, adopting best practices in code structure and commenting. For those looking to enhance their DAX skills, exploring resources on the Enterprise DNA Platform will be beneficial.