Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Camillanaep
Frequent Visitor

how to use a calculated value in the first row to calculate the next row?

Hello everyone!

 

I have a table here wich contains 4 different columns (A   B  C  D)

I input the first value in column A (A1), and the other columns are calculated based on this value (B1  C1  D1).

For the next row, my value calculated in D1 becomes A2, and the operation repeats, calculating (B2  C2  D2). 

 

Does anyone know how can i do this? 

I tried to create a measure, but i can get it right.  

 

Thank you so much!

6 REPLIES 6
erik_tarnvik
Solution Specialist
Solution Specialist

Can you give a real example using real data? What you describe is for sure not possible since you have not provided a termination condition. As described, this process would not terminate and would if nothing else exhaust available memory. But, if you provide a little more insight into the nature of your problem, perhaps there is a way to make progress.

Hey Erik, thank you for answearing!

 

What happens is that i have an excel forecast table (with macro and vba) and i have to move this to power bi.

Basically what my excel table does is "predict", based on an animal inicial wheight and some energy calculations,  a final animal weight per period.   

 

My table is like this:

 

(Wheight0)   (Period)    (Inicial Period Weight)     (Energy1)    (Energy2)     (Energy3)    (Gain)    (Final Wheight)

 

Basically it works like this:

 

  • My column (Wheight0) is the wheight that the animal has when he came to the farm (the real inicial wheight), and it is always the same value.
  • The column "Period" goes from 1 to 15.
  • The "Inicial period wheight" column shows the wheight that the animal has in the beggining of the period, so in the first row (period 1), "inicial period wheight" = "wheight0", and in the following rows, "inicial period wheight" = "Final wheight" from the period before. 
  • "Energy1", "Energy2", "Energy3" and "gain" are calculated based on the "Inicial period Wheight", with some formulas.
  • And "Final wheight" is calculated as a sum of "inicial period wheight" and "gain".

 

So whats happens is:

(an example)

 

 

(Wheight0)   (Period)    (Inicial Period Weight)     (Energy1)    (Energy2)     (Energy3)    (Gain)    (Final Wheight)

 

   100                      1                   100                               0,02              0,27              0,56           4                   104

   100                      2                   104                               0,06              0,29              0,58          3,5                107,5

 

....

 

The termination condition is when the next period is a number lower that the number before.

 

Basically is this.

 

Tell me if you don't understand anything.

 

Thank you very much!

 

Camilla

 

@Camillanaep,

 

Hi Camilla,

 

The formulas of Inicial Period Weight and Final Wheight can't nest each other. There could be loops. So we need to create them independently. Can you share the formula of Gain? 

 

Why they can't nest each other? 

[Inicial Period Weight] could be if ([period] = 1, 100, [Final Wheight]). 

[Final Wheight] could be [Inicial Period Weight] + [gain].

 

When period equals 2, [Inicial Period Weight] = [Final Wheight] while [Final Wheight] = [Inicial Period Weight] + [gain].

 

Best Regards!

Dale

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

Hey Dale, thanks for your answer!

 

The gain formula is:

 

=0,42*(Inicial Period Wheight)

 

And the final wheight formula is:

 

=Gain+Inicial Period Wheight

 

I was trying to use de DAX EARLIER(), but it didn't work

 

Thanks a lot!!

Best,

Camilla

Hi @Camillanaep,

I am not sure you can do this in DAX, but you certainly can in Power Query using the M language. I would regard this as a relatively advanced usage of these tools as you will have to enter M formulas rather than working through the GUI and be comfortable with recursive functions (this is how you implement iterations in functional languages such as M), but anyway, here it is.

 

First, bring up the Power Query window. Click New Source - Blank Query. Click Advanced Editor and copy paste the following into the dialog:

Let
GenerateWeightTable = (StartWeight) => let GWeight = (WeightList, Period, Weight) => if (Period <= 15) then @GWeight(WeightList & {{StartWeight,Period,Weight,1,2,3,0.045*Weight, Weight + 0.045*Weight}},
Period + 1,
Weight + 0.045*Weight) else WeightList in Table.FromRows(GWeight({},1,StartWeight), {"(Weight0)","(Period)","(Initial Period Weight)",
"(Energy1)","(Energy2)","(Energy3)","(Gain)",
"(Final Weight)"}) in GenerateWeightTable

 

I haven't quite figured out what the formulas are supposed to be but you can edit the 

{StartWeight,Period,Weight,1,2,3,0.045*Weight, Weight + 0.045*Weight}

portion as you see fit and replace 1, 2, 3 with whatever formulas you have for these energy coefficients and the same for the gain etc. Also, I have set this so that the recursion stops once Period hits 15. If you have a different termination criteria you can edit the if statement accordingly. Just be careful so that it actually does terminate...

 

You can now execute the function in Power Query by entering a starting weight (100 in your example) and click Invoke. You will get a nice little table with 15 rows as a result. Hope this helps.

Thanks for your answer Erik!

 

I understand your formula, it helped me a lot.

The problem that i have now is to put this code into my power query window, because there's already a code there, showing all the changes that i've done:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\c313355\Documents"), null, true),
    #"Pen Inputs_Sheet" = Source{[Item="Pen Inputs",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"Pen Inputs_Sheet",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type text}, {"Column19", type text}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers1", each ([#"Pen"] <> null))

in

    #"Renamed Columns8"

 

 

This columns that you've created for me (period, wheight, inicial period wheight) already exists in the query, so i just need to reference them in the formulas.

 

Do you know how can i input this formulas into this power query window above? i tried to add another "let" and then put your formulas, put it wasn't right.

 

Thanks!

 

Camilla

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.