Forum Discussion
Solved
Cumulative Total by Year
I have a table with a cumulative total which works well, but it continues to add year on year, instead of resetting each year. What I currently have is this: Running Total column = CALCULAT...
Make sure that you have a date table linked to your sales table, and use columns from the date table in all your visuals.
Either mark the date table as a date table, or use the new calendar options to create a calendar.
You can then write a measure like
Running Total = CALCULATE ( SUM ( 'Sales'[Sales Amount Actual] ), DATESYTD ( 'Date'[Date] ) )
HI RossS,
Option A - Best practice with a Date table
Create a proper Date table and mark it as a Date table.
Use a simple YTD measure that resets each year.
-- Base measure
Sales Amount = SUM('Sales'[Sales Amount Actual])
-- Resets automatically by Year in the visual
Running Total YTD =
CALCULATE(
[Sales Amount],
FILTER(
ALL('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date])
)
)If you prefer a one-liner, this works too:
Running Total YTD = TOTALYTD([Sales Amount], 'Date'[Date])Option B - Keep your table, just stop clearing Year
Replace ALL(Sales) with ALLEXCEPT so the Year stays in context:
Sales Amount = SUM('Sales'[Sales Amount Actual])
Running Total By Year =
CALCULATE(
[Sales Amount],
FILTER(
ALLEXCEPT('Sales', 'Sales'[Year]),
'Sales'[Posting Date] <= MAX('Sales'[Posting Date])
)
)Tip: Make sure you’re using a measure (not a calculated column) for the running total.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.