Forum Discussion

LoganFFS's avatar
LoganFFS
Frequent Visitor
1 year ago
Solved

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 av...
  • Irwan's avatar
    Irwan
    1 year ago

    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.
LoganFFS's avatar
LoganFFS
Frequent Visitor
1 year ago

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

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

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.