2

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

Philip
  • 33
  • 5
  • Is there any business reason stopping you from appending the initial values to the start of your data set? Since then you would have all the data in one table, which would mean that you would get your result with just your [Running_Total_QTY] measure. – Marcus May 15 '18 at 17:10
  • Hi Marcus, thanks. In principle that is the most elegant solution, but I would prefer to avoid appending the data, because I have many more analyzes that need the initial quantity to be left out. – Philip May 16 '18 at 09:31

1 Answers1

0

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.

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • 1
    Thanks! Your first approach works for me, but I had to substitute MAX with LASTNONBLANK because Reporting[ID] is a string returned an error.(I'm running on Excel not Power BI - in case that makes a difference). The second approach also worked with a tweak.In Excel it seems that I can't explicitly define the crossfilter to be bi-directional, but looking at this link [link](https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/) I modified your suggestion to work: `CALCULATE(MAX(Starting_Quantity[ID1]), CROSSFILTER(Reporting[ID],Starting_Quantity[ID1],Both))` – Philip May 16 '18 at 09:27
  • Thanks for pointing out those differences. I work more with Power BI, which does indeed work slightly differently. – Alexis Olson May 16 '18 at 13:57