powerbidaxmeasure

Cumulative total for the last 12 months


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: Chart

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!

Link to PBIX


Solution

  • 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
          )
        )
    

    Result:
    Example result