Search code examples
daxcumulative-sum

Cumulative sum by category with DAX (Without Date dimension)


This is the input data (Let's suppose that I have 14 different products), I need to calculate with DAX, cumulative Total products by Status

ProductID Days Since LastPurchase Status
307255900 76 60 - 180 days
525220000 59 30 - 60 days
209500000 20 < 30 days
312969600 151 60 - 180 days
249300000 52 30 - 60 days
210100000 52 30 - 60 days
304851400 150 60 - 180 days
304851600 150 60 - 180 days
314152700 367 > 180 days
405300000 90 60 - 180 days
314692300 90 60 - 180 days
314692400 53 30 - 60 days
524270000 213 > 180 days
524280000 213 > 180 days

Desire ouput:

Status Cumulative Count
< 30 days 1
> 180 days 4
30 - 60 days 8
60 - 180 days 14

Solution

  • That's trivial: Just take the build in Quick measure "Running total", see screenshot.

    enter image description here

    The resulting table will look like this:

    enter image description here

    However, when you think about it, from a data point of view a sort order like the following makes more sense than ordering "status" by alphabet,

    enter image description here

    and finally you can take it straight away without any crude categorization

    enter image description here