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.

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.

      • kash123's avatar
        kash123
        Frequent Visitor
        4 years ago

        Hi

         

        This is a sample data

         

        TimeSpot RateForward Rate
        10.5 

        2

        		0.6 
        30.7 
        40.8 
        50.9 
        61.0 
        71.1 
        81.2 
        91.3 

         

        I want to implement the formula above into the column forward rates.

         

        Thanks