topic How to check values calculated with ADDCOLUMNS in DAX Commands and Tips

How to check values calculated with ADDCOLUMNS

Peru123 — Fri, 17 Mar 2023 07:26:44 GMT

Hello everyone!

I am encountering a calculation issue with Profit and Loss Calculation. (Posted on the community already but hasn't got any reply)

https://community.powerbi.com/t5/DAX-Commands-and-Tips/FIFO-Profit-and-Loss-Calculation-Calculation-Partially-Correct/td-p/3099795

To check about the issue, I would like to see the invisible values, "New Value" calculated with ADDCOLUMNS in the formula below.

Could anyone please advise me how I can create a column that shows the calculated "New Value"?

P&L (FIFO) = VAR myClient = 'Order History'[Account Number] VAR myUniqueOrder = 'Order History'[Index] VAR myCurrentSell = 'Order History'[Cumulative Sell] VAR myLastSell = 'Order History'[Previous Cumulative Sell] VAR mySymbol = 'Order History'[Instrument] VAR myCumulativeBuy = 'Order History'[Cumulative Buy] VAR myLastCumulativeBuy = 'Order History'[Previous Cumulative Buy] VAR FIFOFilterTable = FILTER ( 'Order History', 'Order History'[Account Number] = myClient && 'Order History'[Instrument] = mySymbol && 'Order History'[Index] < myUniqueOrder && 'Order History'[TransType]= "BUY" && ( ( 'Order History'[Cumulative Buy] >= myLastSell && 'Order History'[Cumulative Buy] < myCurrentSell ) || 'Order History'[Cumulative Buy] >= myCurrentSell && 'Order History'[Previous Cumulative Buy] < myCurrentSell || 'Order History'[Previous Cumulative Buy] > myLastCumulativeBuy && 'Order History'[Cumulative Buy] < myLastCumulativeBuy ) ) VAR FilteredFIFOTable = ADDCOLUMNS ( FIFOFilterTable, "New Value", SWITCH ( TRUE (), 'Order History'[Cumulative Buy] > myLastSell && 'Order History'[Previous Cumulative Buy] < myLastSell, 'Order History'[Qty] - ( myLastSell - 'Order History'[Previous Cumulative Buy] ), 'Order History'[Cumulative Buy] < myCurrentSell, 'Order History'[Qty], -- ELSE -- 'Order History'[Qty] - ( 'Order History'[Cumulative Buy] - myCurrentSell ) ) ) VAR Result = 'Order History'[NetAmountTrade] - SUMX ( FilteredFIFOTable, [New Value] *'Order History'[PricePerShare] ) RETURN IF ( 'Order History'[TransType] = "SELL", Result )

Thank you all in advance.

Peru

Re: How to check values calculated with ADDCOLUMNS

johnt75 — Fri, 17 Mar 2023 09:43:35 GMT

You can use DAX Studio to execute queries, so you could show the entire FilteredFIFO table. You would need to use TREATAS to define the filters for a specific row in the Order History table, and you would need to change the definition of each of the variables to use SELECTEDVALUE instead of relying on row context, e.g.

VAR myClient = SELECTEDVALUE('Order History'[Account Number])

Re: How to check values calculated with ADDCOLUMNS

Peru123 — Tue, 21 Mar 2023 09:13:33 GMT

Hi johnt75

Thank you so much for your advice!

According to your advice,

Done: You can use DAX Studio to execute queries, so you could show the entire FilteredFIFO table.
I could not add TREATAS: You would need to use TREATAS to define the filters for a specific row in the Order History table
Done: you would need to change the definition of each of the variables to use SELECTEDVALUE instead of relying on row context

Could you please advise me where to insert TREATAS?

Here is the query I currently have. With the below query the table output is empty.

DEFINE VAR myClient = SELECTEDVALUE ( 'Order History'[Account Number] ) VAR myUniqueOrder = SELECTEDVALUE ( 'Order History'[Index] ) VAR myCurrentSell = SELECTEDVALUE ( 'Order History'[Cumulative Sell] ) VAR myLastSell = SELECTEDVALUE ( 'Order History'[Previous Cumulative Sell] ) VAR mySymbol = SELECTEDVALUE ( 'Order History'[Instrument] ) VAR myCumulativeBuy = SELECTEDVALUE ( 'Order History'[Cumulative Buy] ) VAR myLastCumulativeBuy = SELECTEDVALUE ( 'Order History'[Previous Cumulative Buy] ) VAR FIFOFilterTable = FILTER ( 'Order History', 'Order History'[Account Number] = myClient && 'Order History'[Instrument] = mySymbol && 'Order History'[Index] < myUniqueOrder && 'Order History'[TransType] = "BUY" && ( ( 'Order History'[Cumulative Buy] >= myLastSell && 'Order History'[Cumulative Buy] < myCurrentSell ) || 'Order History'[Cumulative Buy] >= myCurrentSell && 'Order History'[Previous Cumulative Buy] < myCurrentSell || 'Order History'[Previous Cumulative Buy] > myLastCumulativeBuy && 'Order History'[Cumulative Buy] < myLastCumulativeBuy ) ) EVALUATE ADDCOLUMNS ( FIFOFilterTable, "New Value", SWITCH ( TRUE (), 'Order History'[Cumulative Buy] > myLastSell && 'Order History'[Previous Cumulative Buy] < myLastSell, 'Order History'[Qty] - ( myLastSell - 'Order History'[Previous Cumulative Buy] ), 'Order History'[Cumulative Buy] < myCurrentSell, 'Order History'[Qty], -- ELSE -- 'Order History'[Qty] - ( 'Order History'[Cumulative Buy] - myCurrentSell ) ) )

Re: How to check values calculated with ADDCOLUMNS

johnt75 — Tue, 21 Mar 2023 09:37:24 GMT

You need to tell the engine which row of Order History you want to run the calculation for. The below code assumes that 'Order History'[Index] uniquely identifies a row in the table. If it doesn't then you will need to add additional columns and values to the TREATAS such that they identify a unique row.

DEFINE VAR OrderFilter = TREATAS ( { 1 }, 'Order History'[Index] ) EVALUATE CALCULATETABLE ( VAR myClient = SELECTEDVALUE ( 'Order History'[Account Number] ) VAR myUniqueOrder = SELECTEDVALUE ( 'Order History'[Index] ) VAR myCurrentSell = SELECTEDVALUE ( 'Order History'[Cumulative Sell] ) VAR myLastSell = SELECTEDVALUE ( 'Order History'[Previous Cumulative Sell] ) VAR mySymbol = SELECTEDVALUE ( 'Order History'[Instrument] ) VAR myCumulativeBuy = SELECTEDVALUE ( 'Order History'[Cumulative Buy] ) VAR myLastCumulativeBuy = SELECTEDVALUE ( 'Order History'[Previous Cumulative Buy] ) VAR FIFOFilterTable = FILTER ( 'Order History', 'Order History'[Account Number] = myClient && 'Order History'[Instrument] = mySymbol && 'Order History'[Index] < myUniqueOrder && 'Order History'[TransType] = "BUY" && ( ( 'Order History'[Cumulative Buy] >= myLastSell && 'Order History'[Cumulative Buy] < myCurrentSell ) || 'Order History'[Cumulative Buy] >= myCurrentSell && 'Order History'[Previous Cumulative Buy] < myCurrentSell || 'Order History'[Previous Cumulative Buy] > myLastCumulativeBuy && 'Order History'[Cumulative Buy] < myLastCumulativeBuy ) ) RETURN ADDCOLUMNS ( FIFOFilterTable, "New Value", SWITCH ( TRUE (), 'Order History'[Cumulative Buy] > myLastSell && 'Order History'[Previous Cumulative Buy] < myLastSell, 'Order History'[Qty] - ( myLastSell - 'Order History'[Previous Cumulative Buy] ), 'Order History'[Cumulative Buy] < myCurrentSell, 'Order History'[Qty], -- ELSE -- 'Order History'[Qty] - ( 'Order History'[Cumulative Buy] - myCurrentSell ) ) ), OrderFilter )