Running Total Calculation for the Current Year Shifted to the Same Period Last Year

This guide provides a DAX function to calculate the running total for the current year, shifted to the same period of the previous year. This is useful when you need to compare how the current year's performance measures up to the same timeframe from the last year.

Code Explanation

Function Definition

Below is the DAX code to achieve the specified functionality.

RunningTotalLastYear =
VAR CurrentDate =
    MAX('Date'[Date])
VAR CurrentYear =
    YEAR(CurrentDate)
VAR StartOfYear =
    DATE(CurrentYear, 1, 1)
VAR StartOfLastYear =
    DATE(CurrentYear - 1, 1, 1)
VAR EndOfLastYear =
    EOMONTH(StartOfLastYear, 11) // Adjusting to the end of the last year
RETURN
    CALCULATE(
        SUM('Sales'[SalesAmount]), // Modify 'SalesAmount' and table as necessary
        FILTER(
            ALL('Date'),
            'Date'[Date] <= CurrentDate && 
            'Date'[Date] >= StartOfLastYear // Shifted to the last year's date range
        )
    )

Key Components

• Variables:

  • CurrentDate: Captures the latest date in the filter context.
  • CurrentYear: Determines the current year from CurrentDate.
  • StartOfYear: Marks the beginning of the current year.
  • StartOfLastYear: Marks the beginning of the last year.
  • EndOfLastYear: Marks the end of the last year, used to limit the date range correctly.

• CALCULATE Function:

  • Adjusts the context in which data is evaluated to compute the total sales amount, considering only dates in the previous year.

• FILTER and ALL:

  • Filters out the 'Date' table for context transformations, enabling the desired calculation across all relevant data.

Input Validation

While DAX does not directly implement input validation as in traditional programming languages, ensure that the following conditions are satisfied externally before using this measure:

• The 'Date' table must be a complete date table with proper relationships established.
• The 'Sales' table must exist with a numeric field (in this example, SalesAmount).

Practical Example

To utilize this running total calculation measure in Power BI:

1. Create a Date table with necessary date ranges and columns.
2. Ensure your Sales table is related to the Date table through a date key.
3. Add the RunningTotalLastYear measure to your report visualizations like line charts, cards, or tables to see year-over-year comparisons.

Conclusion

This DAX formula provides an efficient way to generate a running total for sales data that can be compared to previous years, delivering insights into sales trends over time. For more advanced learning and concepts, consider exploring the resources available on the Enterprise DNA platform.