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.

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

    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.