Complex formula in Power BI

[kash123]

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]

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

[Anonymous · Accepted solution]

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]

@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]

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]

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

[kash123]

Hi

This is a sample data

Time	Spot Rate	Forward Rate
1	0.5
2	0.6
3	0.7
4	0.8
5	0.9
6	1.0
7	1.1
8	1.2
9	1.3

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

Thanks

[amitchandak]

@kash123 , I am not sure about t, so I used time. Please find the attached file after signature

new column

Previous Spot Rate = var _max = maxx(filter('Rate', [Time] <EARLIER('Rate'[Time]) ),[Time]) return maxx(FILTER('Rate', 'Rate'[Time] =_max),[Spot Rate])

forward rate = var _max = maxx(filter('Rate', [Time] <EARLIER('Rate'[Time]) ),[Time]) 
return  divide( POWER((1+'Rate'[Spot Rate]),'Rate'[Time]) , POWER((1+'Rate'[Previous Spot Rate]),_max )) -1