DAX Function for Running Total of Previous Years

The following DAX function calculates a running total based on the maximum year selected in a slicer, while displaying the cumulative total for all previous years.

Function Overview

Purpose

To generate a running total for prior years based on the maximum year selected in the slicer.

Parameters

• SalesTable: The table containing sales data, which includes revenue and year columns.

Return Type

• Calculated Column: A running total of sales up to the selected year.

Code Implementation

RunningTotalPreviousYears = 
VAR MaxYearSelected = MAX(SalesTable[Year])
RETURN
    CALCULATE(
        SUM(SalesTable[SalesAmount]),
        FILTER(
            ALL(SalesTable[Year]),
            SalesTable[Year] < MaxYearSelected // Filters to all years less than the max year selected
        )
    )

Explanation of the Code

1. Variable Declaration:

   • MaxYearSelected: Captures the maximum year from the slicer selection using the MAX function.

2. CALCULATE Function:

   • It is used to modify the filter context in which the total sales amount is calculated.

3. SUM Function:

   • Sums the SalesAmount for the specified years defined in the filter.

4. FILTER Function:

   • Returns a table that consists of all years in the SalesTable where the year is less than MaxYearSelected.

5. ALL Function:

   • Ensures that we ignore any existing filters on the Year column so we can analyze the entire dataset.

Usage Example

To utilize this DAX function, add it as a calculated column in a data model in Power BI:

1. Open your Power BI Desktop.
2. In the Data view, select the table where you want to add the calculated column.
3. Paste the DAX code above into the formula bar.
4. Visualize the resulting measure to see the running total of sales for the years prior to the maximum year selected in your slicer.

Additional Notes

• Make sure your dates are formatted correctly, and "SalesAmount" and "Year" are the actual column names in your data model.
• This function performs efficiently as it leverages the powerful DAX context transition capabilities.

Learning Resource

For further improvement of your DAX skills, consider exploring courses available on the Enterprise DNA Platform.

This structured and concise solution should meet your requirements for calculating a running total for previous years based on a user-defined slicer selection in DAX.