Question

How do I build a correct running total for previous years in Power BI using DAX?
I need help building a running total for previous years in Power BI using DAX. My current DAX formula isn't correctly aggregating the values for previous years, and I need guidance on how to modify the formula to calculate the running total correctly for historical data.

Answer 1

To build a correct running total for previous years in Power BI using DAX, you need to adjust your DAX formula to consider the historical data for each year and sum it cumulatively. Here's a step-by-step approach to do this:

Steps for Building a Running Total for Previous Years:

1. Ensure you have a Date Table:

   • Make sure you have a Date Table in your model, and it's related to your fact table. Power BI requires a continuous Date Table for time intelligence functions to work correctly.

   • If you don't have a Date Table, you can create one using this DAX formula:

DateTable = CALENDAR(DATE(2010,1,1), DATE(2030,12,31))

Create a Measure for the Cumulative Total:
To calculate a running total for previous years, you can use the TOTALYTD() or CALCULATE() function with FILTER() to ensure the total includes data for each year cumulatively.

Here’s an example of how to calculate the running total for previous years:

RunningTotalPreviousYears = 
CALCULATE(
   SUM('Sales'[Amount]), 
   FILTER(
      ALL('Date'), 
      'Date'[Date] <= MAX('Date'[Date]) && YEAR('Date'[Date]) < YEAR(TODAY())
   )
)

1. Visualize the Running Total:

   • Now, create a line chart or table in Power BI and use this RunningTotalPreviousYears measure to display the cumulative sum for the previous years.

Example of a Working Formula for Running Total (Excluding Current Year):

RunningTotalPreviousYears = 
CALCULATE(
    SUM('Sales'[Amount]), 
    FILTER(
        ALL('Date'), 
        'Date'[Date] <= MAX('Date'[Date]) && YEAR('Date'[Date]) < YEAR(TODAY())
    )
)

Important Notes:

• ALL('Date') is used to ignore any slicers or filters that might restrict the date range in your report.

• The MAX('Date'[Date]) ensures that the calculation includes data up to the current row's date.

• The YEAR(TODAY()) condition ensures that the running total does not include data from the current year, only from previous years.