Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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
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.
@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])
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
@kash123 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |