A more efficient running total ?

[renegar]

I've calculating a running total based on 4 conditions/filters, the calculated column works on a small scale, but when I used in larger data set (2M rows) it never finished recalculating. I'm wondering if there is a more efficient way. I've read most posts on Running totals, and avoided using calculate vs sumx due to context transition but still. 

Table structure as follows

Qty	SKU	Start	End	ID	Result
10	A	5/1/2019	6/1/2019	A	10
-10	A	5/2/2019	6/1/2019	A	0
40	A	5/2/2019	6/1/2019	B	40

Here is my Calculated Column

=
VAR start1 = Table1[Start]
VAR end1 = Table1[End]
VAR sku1 = Table1[SKU]
VAR subid1 = Table1[ID]
RETURN
SUMX (
FILTER (
Table1,
[Start] <= start1
&& [End] = end1
&& [SKU] = sku1
&& [ID] = subid1
),
Table1[Qty]
)

[v-yuta-msft · Accepted solution]

@renegar ,

SUMX() is an iterator function so it will traverse the whole table, you can use SUM() function instead as below to improve the performance.

Result =
VAR start1 = Table1[Start]
VAR end1 = Table1[End]
VAR sku1 = Table1[SKU]
VAR subid1 = Table1[ID]
RETURN
    CALCULATE (
        SUM ( Table1[Qty] ),
        FILTER (
            Table1,
            [Start] <= start1
                && [End] = end1
                && [SKU] = sku1
                && [ID] = subid1
        )
    )

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hnguy71]

Hi @renegar ,

Instead of using a calculated column, perhaps a measure may suffice? For example, a simple cumculative total measure like this would give you a similar result (for the year):

RunningTotal = CALCULATE(SUM(YOUR_TABLE[Qty]), DATESYTD(YOUR_TABLE[Start]))

[renegar]

Unfortunatetly I can't but, I found that if I do that "[End]=end1" comparison in text (rather than date) it runs pretty fast. Go figure...