topic Re: Complex formula in Power BI in DAX Commands and Tips

Complex formula in Power BI

kash123 — Thu, 30 Dec 2021 09:41:35 GMT

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

Re: Complex formula in Power BI

amitchandak — Thu, 30 Dec 2021 09:44:46 GMT

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

Re: Complex formula in Power BI

kash123 — Thu, 30 Dec 2021 09:52:57 GMT

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

Re: Complex formula in Power BI

amitchandak — Thu, 30 Dec 2021 10:04:06 GMT

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

Re: Complex formula in Power BI

kash123 — Thu, 30 Dec 2021 10:11:11 GMT

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

Re: Complex formula in Power BI

amitchandak — Thu, 30 Dec 2021 10:20:25 GMT

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

Re: Complex formula in Power BI

Anonymous — Mon, 03 Jan 2022 08:20:31 GMT

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.

Re: Complex formula in Power BI

Anonymous — Wed, 12 Jan 2022 05:50:39 GMT

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