Power BI

[LoganFFS]

Hi there, I am new to Power BI and am trying to figure a few things out. Firstly, I want to code a measure for a dataset that calculates the average for a column "share price", but only the first "buy" share price if a different columns previous value was "sell". In simple forms I am trying to calculate the average "share price" when another column is "buy", but only the initial "buy" "share price" after the previous column was a sell. It is kind of tough to explain, but I hope that is clear enough. Thanks in advance!

[v-veshwara-msft · Accepted solution]

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.

[maruthisp]

Hi LoganFFS,
Based on the below sampple data:

Index TradeDate Action SharePrice

1	2025-05-01 09:00 AM	Sell	100.0
2	2025-05-01 10:00 AM	Buy	101.5
3	2025-05-01 11:00 AM	Buy	102.0
4	2025-05-02 09:30 AM	Sell	99.0
5	2025-05-02 10:15 AM	Buy	99.5
6	2025-05-02 11:45 AM	Sell	100.5
7	2025-05-02 12:30 PM	Buy	101.0
8	2025-05-03 09:00 AM	Sell	102.0
9	2025-05-03 09:45 AM	Buy	103.0
10	2025-05-03 11:00 AM	Buy	103.5

Try below DAX measure:

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

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[techies]

Hi @LoganFFS you can create calculated column like this

IsFirstBuyAfterSell =

VAR CurrentIndex = Sheet11[Row]

VAR PrevAction =

    CALCULATE(

        MAX(Sheet11[Action]),

        FILTER(

            Sheet11,

            Sheet11[Row] = CurrentIndex - 1

        )

    )

RETURN

IF(

    Sheet11[Action] = "buy" && PrevAction = "sell",

    1,

    0

)

and then measure as this

AverageFirstBuyPrice =

CALCULATE(

    AVERAGE(Sheet11[Share Price]),

    Sheet11[IsFirstBuyAfterSell] = 1

)