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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
LoganFFS
Regular Visitor

Calculate spread

Hi there, I have been trying to figure out how to get an average price % spread between two actions "buy" and "sell". Ideally I will be able to choose a "name" and a "abbreviation" to see what the average spread between their buys and sells are. I can filter by name and abbreviation currently, but the spread code is what I am really looking for. It is worth noting that it should be between the latest buy before the next sell and the first sell after that buy (not every buy and sell spread averaged). I have attached a picture of a dataset with a few notes. Thanks in advance.

Screenshot 2025-05-21 at 8.30.28 PM.png

1 ACCEPTED SOLUTION

hello @LoganFFS 

 

the previous DAX should be in measure form.

 

but if you want in calculated column, please check if this accomodate your need.

Irwan_0-1748389480410.png

Diff in Calculated Column =
var _BuyDate =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]<=EARLIER('Table'[Date])&&
        'Table'[Name]=EARLIER('Table'[Name])&&
        'Table'[Action]="Buy"
    ),
    'Table'[Date]
)
var _SellDate =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]<=EARLIER('Table'[Date])&&
        'Table'[Name]=EARLIER('Table'[Name])&&
        'Table'[Action]="Sell"
    ),
    'Table'[Date]
)
var _Buy =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]=_BuyDate
    ),
    'Table'[Price]
)
var _Sell =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]=_SellDate
    ),
    'Table'[Price]
)
Return
IF(
    not ISBLANK(_Buy)&&not ISBLANK(_Sell),
    _Sell-_Buy
)
 
Hope this will help.
Thank you.

View solution in original post

8 REPLIES 8
v-tsaipranay
Community Support
Community Support

Hi @LoganFFS  ,

 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

 

Thank you.

v-tsaipranay
Community Support
Community Support

Hi @LoganFFS  ,

 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

 

Thank you.

Hi there this did not work for me. I altered it slightly to apply it to one large data set as a calculated column but it filled a value into every line. I am trying to have it calculate the spread for the first sell after a buy. I can then filter it by the abbreviation and name to get more specific results. If anuyone has an idea on what a calculated column would be to achieve this please let me know. Thanks

Hi @LoganFFS ,

Thank you for reaching out to the Microsoft Fabric Community.

 

I wanted to check if you had the opportunity to review the information provided by @Irwan . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.


Thank you.

hello @LoganFFS 

 

the previous DAX should be in measure form.

 

but if you want in calculated column, please check if this accomodate your need.

Irwan_0-1748389480410.png

Diff in Calculated Column =
var _BuyDate =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]<=EARLIER('Table'[Date])&&
        'Table'[Name]=EARLIER('Table'[Name])&&
        'Table'[Action]="Buy"
    ),
    'Table'[Date]
)
var _SellDate =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]<=EARLIER('Table'[Date])&&
        'Table'[Name]=EARLIER('Table'[Name])&&
        'Table'[Action]="Sell"
    ),
    'Table'[Date]
)
var _Buy =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]=_BuyDate
    ),
    'Table'[Price]
)
var _Sell =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]=_SellDate
    ),
    'Table'[Price]
)
Return
IF(
    not ISBLANK(_Buy)&&not ISBLANK(_Sell),
    _Sell-_Buy
)
 
Hope this will help.
Thank you.

Hi there I have not tried it yet, but i will tomorrow and I'll be sure to let you know. Thanks again!

v-tsaipranay
Community Support
Community Support

Hi @LoganFFS ,

Thank you for reaching out to the Microsoft Fabric Community.

 

I wanted to check if you had the opportunity to review the information provided by @Irwan . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.


Thank you.

Irwan
Super User
Super User

hello @LoganFFS 

 

please check if this accomodate your need.

Irwan_0-1747883351292.png

Irwan_1-1747883421520.png

 

 

create a new measure with following DAX

Diff =
var _Date = SELECTEDVALUE('Table'[Date])
var _Name = SELECTEDVALUE('Table'[Name])
var _BuyDate =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]<=_Date&&
        'Table'[Name]=_Name&&
        'Table'[Action]="Buy"
    ),
    'Table'[Date]
)
var _SellDate =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]<=_Date&&
        'Table'[Name]=_Name&&
        'Table'[Action]="Sell"
    ),
    'Table'[Date]
)
var _Buy =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]=_BuyDate
    ),
  'Table'[Price]
)
var _Sell =
MAXX(
    FILTER(
        ALL('Table'),
        'Table'[Date]=_SellDate
    ),
    'Table'[Price]
)
Return
IF(
    not ISBLANK(_Buy)&&not ISBLANK(_Sell),
    _Sell-_Buy
)
 
Hope this will help.
Thank you.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.