Calculate cumulative rate

[brunoguedes]

Hello,

I would like to calculate the cumulative rate of a query. See the image below:

The correct sum of accumulated rates is 6.29% in the year 2016 - based on the formula highlighted in the image. How do I get to this result?

[brunoguedes]

thanks @MFelix -

It took me a while to implement, but in the end it worked!

[MFelix]

Hi @brunoguedes,

Looking at the print you send and making the calculations based on the formula the result you have is correct, are the monthly values a sum of the daily values?

Regards,

Mfelix

[brunoguedes]

The monthly values are manually entered into a table in MySql - it is not a sum of daily values.

[MFelix]

I did the calculation in excel using the provided formula and values and got to 6.12% and not 6.29%.

Are there any errors in one of the formulas?

Mfelix

[brunoguedes]

Excuse!

I put the formula that does the wrong calculation, it follows in the image the correct form in excel

[MFelix]

To make it easier for me to understands the layout do you have the formula that you wrote in excel as you presented in the 1st post?

[brunoguedes]

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)

[brunoguedes]

ok @MFelix,

Thank you my friend, I'll try it that way. Did you send any attachments?

thanks!!!

[MFelix]

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

[brunoguedes]

Big @MFelix!!!

I am not able to create custom column '' F2 '' - ERROR:

[MFelix · Accepted solution]

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

[brunoguedes]

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?

[MFelix]

@brunoguedes,

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

[MFelix]

---

@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