Search code examples
daxpowerpivotcumulative-sum

DAX running total + starting value

I am fairly new to the DAX universe, but scrolling around I managed to successfully implement a cumulative (running) total, with a measure defined along this structure: Running_Total_QTY:=CALCULATE(SUM(Reporting[QTY]),FILTER(ALL(Reporting[DATE_R]),Reporting[DATE_R]<=MAX(Reporting[DATE_R])))

For a table that looks like this:

ID  DATE_R          QTY
A1  5/11/2018 9:00  5
A1  5/11/2018 9:01  10
A1  5/11/2018 9:01  -5
A1  5/11/2018 9:02  50
A1  5/11/2018 9:05  -20
B1  5/11/2018 9:00  3
B1  5/11/2018 9:01  -20
B1  5/11/2018 9:01  4
B1  5/11/2018 9:02  20
B1  5/11/2018 9:03  10

The problem is that I would need to add to this running total a starting QTY - QTY_INIT, which I receive from another table that looks like this:

ID1 QTY_INIT
A1  100
B1  200

By trial and error I have succeeded by creating a second measure that calculates the average (of 1 item!) defined like this:

Average_starting_quantity:=CALCULATE(AVERAGE(Starting_Quantity[QTY_INIT]),FILTER(ALL(Starting_Quantity[ID1]),Starting_Quantity[ID1]=LASTNONBLANK(Reporting[ID],TRUE())))

And then just adding the two measures together. 

Running_plus_total:=[Running_Total_QTY]+[Average_starting_quantity]

This method works, but is very inefficient and very slow (the data set is quite big).

How can I add QTY_INIT from the second table directly without using a "fake" average (or max, min, etc..)? How can I optimize the measure for a faster performance?

Thanks in advance for any help.

Regards

Solution

  • How about this instead of your Average_starting_quantity?

    StartingQty = LOOKUPVALUE(Starting_Quantity[QTY_INIT],
                          Starting_Quantity[ID1], MAX(Reporting[ID]))

    If your tables are related on ID and ID1 with cross filter direction going both ways,

    enter image description here

    then you can just use

    StartingQty = MAX(Starting_Quantity[QTY_INIT])

    since the filter context on ID will flow through to ID1.