Forum Discussion

LoganFFS's avatar
LoganFFS
Frequent Visitor
1 year ago
Solved

Dax Measure

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 ave...
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    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

     

Poojara_D12's avatar
Poojara_D12
Icon for Super User rankSuper User
1 year ago

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.