Fix running balances for dates when both purchase AND sale transactions occur (multiple criteria)

[leolapa_br]

I have an imported table from Excel via PQ 'fTrans' that contains the columns 'Ticker', 'Date', 'Shares' and 'Transaction'.

Then I created the following measure:

Shares Balance = 
CALCULATE( 
    SUM( fTrans[Shares] ), 
    ALLEXCEPT( fTrans, fTrans[Ticker] ), 
    fTrans[Transaction] = "Purchase", 
    fTrans[Date] <= MAX( fTrans[Date] )
) - 
CALCULATE( 
    SUM( fTrans[Shares] ), 
    ALLEXCEPT( fTrans, fTrans[Ticker] ), 
    fTrans[Transaction] = "Sale", 
    fTrans[Date] <= MAX( fTrans[Date] )
)

Which I pasted as an additional column to the following table visual:

                                                                     Shares

Ticker   Date                 Shares Transaction Balance

--------------------------------------------------------

ASAI3   03/Mar/2023      57     Purchase     57

ASAI3   08/May/2023     92     Purchase    149

ASAI3   09/Jun/2023      149    Sale             0

ASAI3   19/Jul/2023       81      Purchase     81

ASAI3   16/Aug/2023     81      Sale             0

AURE3   23/Feb/2021    42       Purchase     42

AURE3   01/Jun/2023    45       Purchase     87

AURE3   03/Aug/2023   47       Purchase     92           (134)

AURE3   03/Aug/2023   42       Sale             92

AURE3   05/Oct/2023    48       Purchase     95           (140)

AURE3   05/Oct/2023    45       Sale             95

AURE3   03/Nov/2021   95       Sale             0

Since the measure accounts for all purchases AND sales occurred on the same day, the 'Shares Balance' column shows the same balance for both rows, but what I really wanted for the purchase transaction row was to get the balance prior to the sale transaction taking place (numbers in parenthesis).

How can I tweak the above DAX code to get that?

Thanks in advance!

[Jihwan_Kim · Accepted solution]

Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

expected result measure: =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (
            ALL ( fTrans ),
            fTrans[Ticker],
            fTrans[Date],
            fTrans[Transaction],
            fTrans[Shares]
        ),
        "@transindex",
            SWITCH (
                TRUE (),
                fTrans[Transaction] = "Sale", 2,
                fTrans[Transaction] = "Purchase", 1
            ),
        "@transvalue",
            SWITCH (
                TRUE (),
                fTrans[Transaction] = "Sale", CALCULATE ( SUM ( fTrans[Shares] ) ) * -1,
                fTrans[Transaction] = "Purchase", CALCULATE ( SUM ( fTrans[Shares] ) )
            )
    )
RETURN
    IF (
        HASONEVALUE ( fTrans[Ticker] ),
        SUMX (
            WINDOW (
                1,
                ABS,
                0,
                REL,
                _t,
                ORDERBY ( fTrans[Date], ASC, [@transindex], ASC ),
                ,
                PARTITIONBY ( fTrans[Ticker] )
            ),
            [@transvalue]
        )
    )

[leolapa_br]

@Jihwan_Kim your solution works perfectly, thanks a lot!

There is one particular thing though about a table visual I've got on my Power BI data model.

This particular visual contains 2 date columns: the fact date called "Data" which is the date each transaction took place and a second column that I need for another purpose not worth explaining.

The thing is as you can see on the screenshot below when the visual brings both dates columns the column with the shares balance measure you helped me create (here called "Saldo de cotas") ends up bringing weird results:

As soon as I remove that second date column "Data da venda relacionada à respectiva compra" from the visual the shares balance measure is back to normal, bringing the results that are meant to come.

Is there a way to add an element to the DAX code that could shield the solution from that second dates column? Something like ALL or ALLSELECTED somewhere in the formula so once that particular table visual comes with both dates columns the measure column still brings the correct results?