I'm trying to visualize sales for the last 12 months, but with a specific twist:
I have a year-month slicer (from a calendar table) that defines the last visible month.
The chart should then show 12 months back from the selected date.
However, the cumulative total should restart at the beginning of each year.
(On the page will be other visuals, which are also filtered by the year-month slicer, and also there will be more slicers - therefore I can't keep calendar disconnected).
Example Given the following data:
Date | Original_amt | Cumulated |
---|---|---|
2024-01 | 10 | 10 |
2024-02 | 10 | 20 |
2024-03 | 10 | 30 |
2024-04 | 10 | 40 |
2024-05 | 10 | 50 |
2024-06 | 10 | 60 |
2024-07 | 10 | 70 |
2024-08 | 10 | 80 |
2024-09 | 10 | 90 |
2024-10 | 10 | 100 |
2024-11 | 10 | 110 |
2024-12 | 10 | 120 |
2025-01 | 10 | 10 |
2025-02 | 10 | 20 |
2025-03 | 10 | 30 |
2025-04 | 10 | 40 |
2025-05 | 10 | 50 |
2025-06 | 10 | 60 |
2025-07 | 60 | |
2025-08 | 60 | |
2025-09 | 60 | |
2025-10 | 60 | |
2025-11 | 60 | |
2025-12 | 60 |
If I select 2025-08 in the slicer, I expect to see this in the visual:
What works so far I created a measure (Cumulated with hist) that gives the correct visual result
Problem To make the cumulative measure work, I had to remove all filters inside the DAX calculation. This unfortunately, means that any additional slicers on the page (e.g. by department, region, product group) are ignored, which is not acceptable.
Sales Cumulated =
CALCULATE(
[Sales Toys],
FILTER(
ALL('fact_sales'), -- Removes any filter on the fact table's date column
'fact_sales'[YM] <= MAX('fact_sales'[YM]) -- Calculate the cumulative sum until the selected date
))
Question How can I:
Accumulate values that restart each year, and
Still respect all other filters on the page?
Any ideas or workarounds are welcome — thank you!
Try this singular Measure (comments added inline).
Chart Sales YTD =
// set the range needed from slicer (like you've done)
var dateEnd = MAX('dim_kalendar'[Date])
var dateStart = EDATE(dateEnd, -12)
RETURN
CALCULATE(
// using Time Intelligence function TOTALYTD
TOTALYTD( SUM(fact_sales[Sales]), fact_sales[YM] ),
// remove date slicer context (to get all dates)
REMOVEFILTERS(dim_kalendar),
// KEEPFILTERS needed to maintain date context on the visual, and filter on the range
KEEPFILTERS(
fact_sales[YM] >= dateStart && fact_sales[YM] <= dateEnd
)
)