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 col...
  • 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.

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.