Calculating a factor

[ZodtePt]

Hi, I'm trying to calculate the column "Fator" and the calculous is pretty simple. I just want to do this math operation:

FATOR = "Previous Factor" * ("Taxa CDI Deslocada"+1)^(("Dias úteis" - "Dias Úteis Deslocada")/252)-1

for the first line "Previous Factor" = 100; for all the other lines "Previous Factor" is the FATOR calculated in the line above.

Although it appears to be simple math, I don't know how to write it in Dax.

[v-tangjie-msft]

Hi @ZodtePt ,

Based on your needs, I would like to ask how the 100 in your phrase "for the first line "Previous Factor" = 100" is reflected in the sample data you provided? For the calculation needs of the power you say, you can try to use the Power() function in DAX, or if you want to get the specific value of the row in the measure, you can use the Max() function to get it.

POWER function (DAX) - DAX | Microsoft Learn

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[ZodtePt]

Hi @v-tangjie-msft, thanks for your help!

as I mentioned the idea is to be able to create a column called FATOR where the result needs to follow this logic:
FATOR = "Previous Factor" * ("Taxa CDI Deslocada"+1)^(("Dias úteis" - "Dias Úteis Deslocada")/252)-1

Since there is no "Previous Factor" for the first row (because it's the first one), the logic for the FATOR in the first row should be:

FATOR = 100 * ("Taxa CDI Deslocada"+1)^(("Dias úteis" - "Dias Úteis Deslocada")/252)-1

for all other rows, the logic is: the FATOR from row "n" has as "Previous Factor" the FATOR from row "n-1"

FATOR(n) = FATOR(n-1) * (Taxa CDI Deslocada(n)+1)^((Dias úteis(n) - Dias Úteis Deslocada(n))/252)-1

n=row number

for the first row FATOR(n-1) = 100

this helped?

[v-tangjie-msft · Accepted solution]

Hi  @ZodtePt ,

Depending on your requirements description, you want to implement logic that implements loop iteration multiplication by referencing the value of the "factor" on the previous line, right? The logic of the first line should be derived like this, right?

100*(0.1365+1)^((42-0)/252)-1

If this is the case, DAX can not implement the loop iteration multiplication logic you want to get, I recommend that you use the For loop in Python to implement this logic, convenient and efficient, and then use Python to implement this logic, you can in turn try to use PBI desktop's Python data source connector to connect to this processed data, and continue to create the corresponding visual according to your needs:

Python For Loops (w3schools.com)

Run Python Scripts in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly.