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)
Super User
brunoguedes wrote: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)
Hi brunoguedes,
I hjave been working on your problem and I have found a way, for now it's not very clean but it's working however it's not done in DAX but in M, so trough the power query. Do you usually work with the Edit Queries?
In the attach file you can see I calculate a F for each month and the for the next F I get the previous indexed value and make it as you formula so basically I'm getting:
F = (IPCA-E current month * F previous month) + IPCA-E current month + F previous month
Then after all the F are calculated I make a single column and delete the other that i don't need, like that my final data for the graphs has one additional column with the acumulated rate. This is working for multiple years so each year returns to 0 your only concern in this is to have the table sorted by dates.
Again it's not pretty but it works.
I have limited time during this week to see it in more detail but please check it and send any questions or remarks.
Regards,
MFelix