cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ZodtePt
New Member

Calculating a factor

ZodtePt_0-1670348596570.png

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.

 

 

 

 

1 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

 

vtangjiemsft_1-1670571650226.png

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. 

View solution in original post

3 REPLIES 3
v-tangjie-msft
Community Support
Community Support

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. 

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?

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

 

vtangjiemsft_1-1670571650226.png

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. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors