DAX Running Total with Buckets

Question

I'm newish to Power BI/DAX, and I'm having trouble getting a running total to work the way I need. Assume the following table for data:

User    month   sales
UserA   1/1/2019    1
UserB   1/1/2019    3
UserC   1/1/2019    2
UserA   2/1/2019    1
UserB   2/1/2019    3
UserC   2/1/2019    2
UserA   3/1/2019    1
UserB   3/1/2019    3
UserC   3/1/2019    2

I've been looking around and I've found the following formula gives me a good running total the way I need:

AllSales = 
calculate(
    sum('table'[Sales]),
    filter(
        all ('table'),
        'table'[date] <= max ('table'[date])
        )
)

--

Total   6   12  18  18

The problem comes when I want to see this in matrix form with the users breaking out into buckets. When I do this, the number of sales is the same for each user:

UserA   6   12  18  18
UserB   6   12  18  18
UserC   6   12  18  18
Total   6   12  18  18

My desired outcome would look like this:

UserA   1   2   3   3
UserB   3   6   9   9
UserC   2   4   6   6
Total   6   12  18  18

I believe I understand why the ALL function is causing this, but I don't know how to tweak it or which function to switch to in order to resolve this issue. Any help would be very much appreciated. Thanks!

RADO RADO · Accepted Answer · 2019-12-31T20:19:42

Instead of applying ALL to the entire table, apply it only to the column you need:

AllSales =
CALCULATE (
    SUM ( 'table'[Sales] ),
    FILTER ( ALL ( 'table'[date] ), 'table'[date] <= MAX ( 'table'[date] ) )
)

DAX Running Total with Buckets

1 Answers