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.
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.