Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
leolapa_br
Resolver I
Resolver I

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

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!
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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

 

Jihwan_Kim_0-1692245413083.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

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

 

Jihwan_Kim_0-1692245413083.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@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:

 

PBI_Issue.png

 

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?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors