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.
Irwan's avatar
Irwan
Icon for Super User rankSuper User
1 year ago

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.