Running Total DAX not working with multiple filters

[Nabha-Ahmed]

Hi community,

i need help with a tricky DAX problem
I’m trying to create a Running Total (Cumulative Sales) measure in Power BI.

What I did:

Running Total =
CALCULATE(
SUM('Sales'[Amount]),
FILTER(
ALL('Sales'[Date]),
'Sales'[Date] <= MAX('Sales'[Date])
)
)

It works fine when I only use Date.
 But the issue is:

• When I add Product Category or Region as a filter, the running total resets unexpectedly.

• For example, if I select one category, the total only runs inside that category instead of across all data.

  What I need:

  • A true running total across the full dataset (global cumulative sales),

  • but still able to slice by Category, Region, or Customer without breaking the calculation.

    I’ve tried ALLSELECTED(), REMOVEFILTERS(), and even VAR + SUMX, but I still can’t get it right.

     Question:
    What’s the best practice DAX formula for running totals that work correctly with multiple slicers and filters?

    Thanks in advance for any help! 

[danextian]

Hi @Nabha-Ahmed 

That is because the filter modifier is applied to the date column only.  Now, if you want the cumulative calculation to not reset regardless of the slicer selection, try applying that to the whole table. You can see in the image below that the cumulative value doesn't reset regardless of the category name but instead shows the same amount for each

date.

Running Total =
CALCULATE(
SUM('Sales'[Amount]),
FILTER(
ALL('Sales'),
'Sales'[Date] <= MAX('Sales'[Date])
)
)

[v-menakakota]

Hi @Nabha-Ahmed  , 

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @danextian , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

    

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @Nabha-Ahmed ,

Can you please confirm whether the issue is sorted or not.

Thank you.

[v-menakakota]

Hi @Nabha-Ahmed ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team 

[v-menakakota]

Hi @Nabha-Ahmed ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team 

[Shahid12523]

Global running total (ignores Category/Region filters):

Running Total =
CALCULATE (
SUM ( 'Sales'[Amount] ),
FILTER ( ALL ( 'Sales' ), 'Sales'[Date] <= MAX ( 'Sales'[Date] ) )
)

Running total per Category/Region (respects slicers):

Running Total =
CALCULATE (
SUM ( 'Sales'[Amount] ),
FILTER ( ALLSELECTED ( 'Sales'[Date] ), 'Sales'[Date] <= MAX ( 'Sales'[Date] ) )
)

Pick ALL for global, ALLSELECTED for slicer-aware.

[Ashish_Mathur]

Hi,

Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort he Month name column by the Month number column.  Create a relationship (Many to One and Single) from the Date column of the Sales table to the Date column of the Calendar Table.  To your visual, drag Year/Month name from the Clendar Table.  Write this measure

Running Total = CALCULATE(SUM('Sales'[Amount]),datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date]),all(category),all(Region),all(Customer))