Question

How to write a DAX running total that respects its own resource hierarchy and resets appropriately?

I need a way to create a DAX running total that respects the hierarchy in my data and resets at the appropriate levels. I’m looking for an automated solution to ensure consistency in reporting.

Answer 1

To create a DAX running total that respects the hierarchy and resets at appropriate levels, you can use the CALCULATE, SUM, and FILTER functions along with the ALL function to remove filters at the desired hierarchy levels. Here's an example:

RunningTotal =
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'[HierarchyLevel1], 'Table'[HierarchyLevel2]),
        'Table'[Date] <= MAX('Table'[Date])
    )
)

In this example, replace 'Table'[Amount] with the column you want to sum and adjust the hierarchy levels (HierarchyLevel1, HierarchyLevel2) to match your data model. This will reset the total at each new hierarchy level while calculating the running total.