Power BI

Hi LoganFFS ,

Thanks for posting your scenario in Microsoft Fabric Community 

Also thanks to techies and maruthisp who shared two effective approaches.

I tested both solutions using a sample dataset in Power BI and can confirm they return the expected result.

Dataset Used:
I created a table named Trades with the following data:

 

Method 1:  Using a Calculated Column
I used the below calculated column to flag each “Buy” row that directly follows a “Sell”:

IsFirstBuyAfterSell = 
VAR CurrentIndex = Trades[Index]
VAR PrevAction =
    CALCULATE(
        MAX(Trades[Action]),
        FILTER(
            Trades,
            Trades[Index] = CurrentIndex - 1
        )
    )
RETURN
IF(
    Trades[Action] = "Buy" && PrevAction = "Sell",
    1,
    0
)

This resulted in addition of new column as below:

Then I used this measure:

AverageFirstBuyPrice = 
CALCULATE(
    AVERAGE(Trades[SharePrice]),
    Trades[IsFirstBuyAfterSell] = 1
)

 

Output: Card visual showing the result: 101.25.

 

Method 2: Pure DAX Measure (No Calculated Columns)
This solution uses ADDCOLUMNS and TOPN to dynamically check the previous row for each "Buy":

AvgFirstBuyAfterSell = 
AVERAGEX(
    FILTER(
        ADDCOLUMNS(
            Trades,
            "PrevAction",
            VAR CurrIndex = Trades[Index]
            RETURN
            CALCULATE(
                VALUES(Trades[Action]),
                TOPN(
                    1,
                    FILTER(ALL(Trades), Trades[Index] < CurrIndex),
                    Trades[Index], DESC
                )
            )
        ),
        Trades[Action] = "Buy"
        && [PrevAction] = "Sell"
    ),
    Trades[SharePrice]
)

Output: Card visual for the AvgFirstBuyAfterSell measure showing the same result: 101.25.

 

 

Summary of both Methods:
Method 1 is easier to read and debug, especially for smaller datasets or when you prefer to work with columns.

Method 2 is more dynamic and avoids adding columns to your model - ideal for scalable reports.

Both solutions correctly average the first “Buy” share prices after a “Sell”:
(101.5 + 99.5 + 101.0 + 103.0) / 4 = 101.25

 

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.