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

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.