topic Re: Fix running balances for dates when both purchase AND sale transactions occur (multiple criteria) in DAX Commands and Tips

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

leolapa_br — Thu, 17 Aug 2023 01:06:18 GMT

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!

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

Jihwan_Kim — Thu, 17 Aug 2023 04:10:50 GMT

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

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

leolapa_br — Thu, 17 Aug 2023 18:29:28 GMT

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?