Forum Discussion
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.
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)&¬ ISBLANK(_Sell),
_Sell-_Buy
)Hope this will help.Thank you.
8 Replies
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)&¬ ISBLANK(_Sell),
_Sell-_Buy
)Hope this will help.Thank you.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.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 I have not tried it yet, but i will tomorrow and I'll be sure to let you know. Thanks again!
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
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)&¬ ISBLANK(_Sell),
_Sell-_Buy
)Hope this will help.Thank you.
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.
