Dax Measure

[LoganFFS]

Hi there, I am hoping someone has a solution to this problem. I have a data set in my dashboard with "date", "name", "symbol", "action" and "price". I am trying to figure out how to calculate the average price of the first "buy" after a "sell"(which are both in the action column). I have run into trouble because there are multiple buys and sells on the same "date", and no timestamp to differentiate. Ideally I am able to calculate the average first buy after a sell, whether it needs to be done with a measure and a caculated column or just a measure. Anyways any help is appreciated. Thanks! 

[Poojara_D12]

Hi @LoganFFS 

1: Create an Index column in Power Query or using DAX

If you can use Power Query, add an Index Column after sorting the data by Date and Name or Symbol. If you must do it in DAX:

ActionIndex = 
RANKX(
    FILTER(
        'Table', 
        'Table'[Name] = EARLIER('Table'[Name])
    ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

This gives a sequence number per name sorted by date.

2: Find the next buy index after each sell

Create a calculated column to find the next buy index for each sell:

NextBuyIndex = 
CALCULATE(
    MIN('Table'[ActionIndex]),
    FILTER(
        'Table',
        'Table'[Name] = EARLIER('Table'[Name]) &&
        'Table'[Action] = "buy" &&
        'Table'[ActionIndex] > EARLIER('Table'[ActionIndex])
    )
)

This finds the earliest buy after the sell for the same Name.

3: Retrieve the price of that next buy

Add a calculated column:

PriceOfNextBuy = 
LOOKUPVALUE(
    'Table'[Price],
    'Table'[Name], 'Table'[Name],
    'Table'[ActionIndex], 'Table'[NextBuyIndex]
)

4: Calculate average price of these first buys

Finally, create a measure to average all such next buy prices for sells:

AvgPriceFirstBuyAfterSell = 
AVERAGEX(
    FILTER('Table', 'Table'[Action] = "sell" && NOT(ISBLANK('Table'[PriceOfNextBuy]))),
    'Table'[PriceOfNextBuy]
)

By indexing actions per entity ordered by date, then for each sell finding the next buy, retrieving its price, and averaging those prices, you can calculate the average price of the first buy after a sell, even if multiple actions occur on the same day with no timestamp.

This solution requires creating calculated columns and measures in DAX, and assumes you can establish a consistent ordering of actions per entity by date (and possibly by some stable secondary sorting if needed). This is the best way to handle sequencing without timestamps in Power BI.

[v-dineshya · Accepted solution]

Hi @LoganFFS ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Please follow below steps.

Step 1: Add an Index Column, Since there’s no timestamp, we need an index column that gives us the row order. Do this in
Power Query:

Sort by Date, Name, Symbol. Add an Index column starting from 1 (call it RowIndex).

Step 2:DAX Measure to Get First Buy After Each Sell

Create a measure with below DAX

AverageFirstBuyAfterSell =
VAR SellTable =
FILTER (
ALL ( Table ),
Table[Action] = "Sell"
)
VAR FirstBuyAfterSellTable =
ADDCOLUMNS (
SellTable,
"FirstBuyPrice",
CALCULATE (
MINX (
TOPN (
1,
FILTER (
ALL ( Table ),
Table[RowIndex] > EARLIER ( Table[RowIndex] )
&& Table[Action] = "Buy"
),
Table[RowIndex], ASC
),
Table[Price]
)
)
)
RETURN
AVERAGEX (
FirstBuyAfterSellTable,
[FirstBuyPrice]
)

Step 3 : Drag all the fields in table visual.

Please refer sample data and output snaps and PBIX file.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @LoganFFS ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[v-dineshya]

Hi @LoganFFS ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[v-dineshya]

Hi @LoganFFS ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thank you.

[maruthisp]

Hi LoganFFS,

Please find the attached pbix file with a solution for you problem description.
Dax Measure.pbix

Please let me know if you have further questions.

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

[LoganFFS]

Hi there, I tried it today. The index column I added in just fine, but the measure gave me trouble. When I entered the measure, I entered it into a number visual hoping to display the average instead of at the bottom of the dataset. I attached a photo of what i hope to achieve. Whether it needs to be done in one measure or two calculated columns im not sure. I only want to have a value in the new column where there is a buy directly after a sell so that I can average that column easier. This should hopefully clear up what im trying to achieve. Thanks in advance!