How to check values calculated with ADDCOLUMNS

[Peru123]

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-... 

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

[johnt75]

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])

[Peru123]

Hi @johnt75 

Thank you so much for your advice!

According to your advice, 

1. Done: You can use DAX Studio to execute queries, so you could show the entire FilteredFIFO table.
2. I could not add TREATAS: You would need to use TREATAS to define the filters for a specific row in the Order History table
3. 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 )
            )
    )

 

[johnt75 · Accepted solution]

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
)