topic Re: Calculate spread in DAX Commands and Tips

Calculate spread

LoganFFS — Thu, 22 May 2025 02:33:44 GMT

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.

Re: Calculate spread

Irwan — Thu, 22 May 2025 03:11:37 GMT

hello LoganFFS

please check if this accomodate your need.

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.

Re: Calculate spread

Anonymous — Fri, 23 May 2025 08:18:15 GMT

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.

Re: Calculate spread

Anonymous — Mon, 26 May 2025 05:18:47 GMT

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.

Re: Calculate spread

LoganFFS — Tue, 27 May 2025 03:17:31 GMT

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

Re: Calculate spread

LoganFFS — Tue, 27 May 2025 23:33:24 GMT

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

Re: Calculate spread

Irwan — Tue, 27 May 2025 23:45:40 GMT

hello LoganFFS

the previous DAX should be in measure form.

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

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.

Re: Calculate spread

Anonymous — Thu, 29 May 2025 05:23:44 GMT

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.

Re: Calculate spread

Anonymous — Mon, 02 Jun 2025 03:48:16 GMT

Hi @LoganFFS ,

Thank you.