Running total over dates
A cumulative total that adds up everything from the first visible date to the current one.
Running Total Sales =
VAR LastVisibleDate = MAX('Date'[Date])
RETURN
CALCULATE(
[Total Sales],
FILTER(
ALL('Date'[Date]),
'Date'[Date] <= LastVisibleDate
)
)How it works
MAX('Date'[Date]) is the last date of the row in the visual. ALL('Date'[Date]) removes the date filter coming from the visual, FILTER then keeps every date up to that last date, and CALCULATE sums [Total Sales] over them.
Because only the Date column is cleared, other filters (a year slicer, a region slicer) still apply, so the running total starts at the first selected date.
What your model needs
- A date table named 'Date' with one row per day and no gaps, marked as the date table in Power BI (Table tools > Mark as date table). - A one-to-many relationship from 'Date'[Date] to the date column of the Sales table (for example Sales[OrderDate]). - Total Sales = SUM(Sales[Amount]) (called [Total Sales]).
Small example
Input data
Day | Total Sales
1 Jan | 40
2 Jan | 25
3 Jan | 35Expected result
Day | Running Total Sales
1 Jan | 40
2 Jan | 65
3 Jan | 100Common mistakes and limitations
- Removing filters from the whole Date table (ALL('Date')) also removes year slicers, so the total would start from the very first date in the model. - On a visual that does not show dates (for example a card) the measure returns the total up to the latest date in context.