Forum Discussion
Calculate cumulative rate
HI brunoguedes,
In the formula the F1 without the bracets is refering to the name of the step in the query so you should refer to the previous step that is Renamed Columns, the easist way is to change that Renamed to F1 and then see the result, after that all of the customs colums you add should be change to F2, F3,... that way you will get the desired result.
Breaking down the formula for you:
if Date.Month([Date])=2 then (F1{[Index]-1}[F1]*[Value])+F1{[Index]-1}[F1]+[Value] else 0)
F1 = Refers to the name of the step you want to get information from
{[Index]-1} = Refers to the row in wich you want to get the information from the previous step use index column just to get the row number since index it's consecutive numbers the index of the current row -1 give you the previous row
[F1] = refers to the column you want to get the information from
What happened and for simplification terms I ussualy give the step and the new column the same name so that I can easily go back and forward in my code to reference everything.
Additional question just curious are you from Portugal?
Regards,
MFelix
The formula for the first post is wrong, you need to calculate the interest accumulated in the selected months. Power Bi is just adding up the values so we have the result of 6.12% - it's a simple sum. I need a sum based on interest on interest (cumulative income)
- MFelix
Super User
I tried to add the file but with no luck.
Please go to the shared file below.
https://drive.google.com/drive/folders/0B4MldU-uGEG3WGJsVWNZMTM1aWM
Regards
Mfelix
- MFelix
HI brunoguedes,
In the formula the F1 without the bracets is refering to the name of the step in the query so you should refer to the previous step that is Renamed Columns, the easist way is to change that Renamed to F1 and then see the result, after that all of the customs colums you add should be change to F2, F3,... that way you will get the desired result.
Breaking down the formula for you:
if Date.Month([Date])=2 then (F1{[Index]-1}[F1]*[Value])+F1{[Index]-1}[F1]+[Value] else 0)
F1 = Refers to the name of the step you want to get information from
{[Index]-1} = Refers to the row in wich you want to get the information from the previous step use index column just to get the row number since index it's consecutive numbers the index of the current row -1 give you the previous row
[F1] = refers to the column you want to get the information from
What happened and for simplification terms I ussualy give the step and the new column the same name so that I can easily go back and forward in my code to reference everything.
Additional question just curious are you from Portugal?
Regards,
MFelix
I got it! I'll try to finish and show you.
I'm from Brazil, I'm using translater - sorry for my English! you speak Portuguese?- MFelixbrunoguedes,
Hope it works will try to make a better way to do it in only one column next week if i can do it I will send it to you.
I'm from Portugal, best luck if you can make it work mark the that is a response.
Regards