Forum Discussion

kash123's avatar
kash123
Frequent Visitor
4 years ago
Solved

Complex formula in Power BI

HI 

 

I want to implement a spot rate to forward rate formula in PowerBi: Is this possible? 

 

I already have the spot rates in a column loaded from an SQL data base, how would I create a new column for the forward rates?

 

The formula for spot rate to forward rate conversion is 

 

[(1 + current spot rate)^t / (1+ prev spot rate)^t-1]-1

 

The trouble I am having is referencing the previous spot rates with the t before. 

 

Thanks 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi kash123 ,

     

     [(1 + current spot rate)^t / (1+ prev spot rate)^t-1]-1   What does "t" in this formlua stand for? 

     

    If you do not know how to get the previous value , since the Time is continuously, you could use 

    _previousRate= CALCULATE(MAX('Table'[Spot Rate]),FILTER('Table',[Spot Rate]=EARLIER('Table'[Spot Rate])-1))

    Like:

    Forward Rate = 
var _previousRate= CALCULATE(MAX('Table'[Spot Rate]),FILTER('Table',[Spot Rate]=EARLIER('Table'[Spot Rate])-1))
return  POWER( 1+ [Spot Rate], [Time] )/ POWER( 1+_previousRate, [Time]-1)  -1

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    kash123 , Assume you have two columns

    date and spot rate and date are not continuous

     

    Then previous rate in a new columns

     

    Last rate =

    var _max = maxx(filter(Table, [Date] < earlier([date]) , [Date])

    return

    maxx(filter(Table, [Date] =_max) , [Spot Rate])

    • kash123's avatar
      kash123
      Frequent Visitor
      4 years ago

      Hi Amit 

       

      Thank you for your response! How do I implement the formula you have given me, is each line in a new column/measure

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        kash123 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi kash123 ,

     

     [(1 + current spot rate)^t / (1+ prev spot rate)^t-1]-1   What does "t" in this formlua stand for? 

     

    If you do not know how to get the previous value , since the Time is continuously, you could use 

    _previousRate= CALCULATE(MAX('Table'[Spot Rate]),FILTER('Table',[Spot Rate]=EARLIER('Table'[Spot Rate])-1))

    Like:

    Forward Rate = 
var _previousRate= CALCULATE(MAX('Table'[Spot Rate]),FILTER('Table',[Spot Rate]=EARLIER('Table'[Spot Rate])-1))
return  POWER( 1+ [Spot Rate], [Time] )/ POWER( 1+_previousRate, [Time]-1)  -1

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi kash123 ,

     

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file afterremoving sensitive data.

     

    Best Regards,
    Eyelyn Qin