Is it possible to use a value in a row to calculate the next value in the same row?

[Anonymous]

I need to make a calculus that set the first value as 100 and the others are calculated based in the variation month by month. 

It's a simple calculus, but I really don't know how to make it in power bi. I don't even know if is possible to do something like that because of the way power bi works. 

In Excel it would be a really straightforward calculus. 

I appreciate if someone could help me.

month	price	variation	100 base
dez/19	1	-	100
jan/20	1,34	34%	134
fev/20	1,43	7%	143
mar/20	1,43	0%	143
abr/20	1,43	0%	143
mai/20	1,43	0%	143
jun/20	1,43	0%	143
jul/20	1,5	5%	150
ago/20	1,5	0%	150
set/20	1,5	0%	150
out/20	1,5	0%	150
nov/20	1,5	0%	150
dez/20	1,5	0%	150

[MFelix · Accepted solution]

Hi @Anonymous ,

In DAX is not possible to make recursive values, so calculating a value based on the calculation of itself. Has you refer this is easy in excel but DAX does not allows this.

In Power Query you can do it check the articles below with some examples that you can adjust to your needs:

https://www.thebiccountant.com/2017/09/26/recursion-m-beginners/

https://www.thepoweruser.com/2019/07/01/recursive-functions-in-power-bi-power-query/

https://xcelanz.com/recursive-functions-in-power-query/

[MFelix · Accepted solution]

Hi @Anonymous ,

In DAX is not possible to make recursive values, so calculating a value based on the calculation of itself. Has you refer this is easy in excel but DAX does not allows this.

In Power Query you can do it check the articles below with some examples that you can adjust to your needs:

https://www.thebiccountant.com/2017/09/26/recursion-m-beginners/

https://www.thepoweruser.com/2019/07/01/recursive-functions-in-power-bi-power-query/

https://xcelanz.com/recursive-functions-in-power-query/

[SteveHailey · Accepted solution]

Hello @Anonymous. 

You can easily do this in the power query editor. To access the previous (or next) row, you can add an Index from 0 and an Index from 1, and then do a merge to join the table with itself, on those two columns. I created a pbix here that you can download.

I started with this data:

and ended with this:

You can step through each step in the Applied Steps pane to see how it works.

If you have any questions at all just let me know and I'll be happy to go through it in more detail.

I will also paste the M code below, although in this case I think it would be more instructive to open the .pbix and go through the steps, because it was all done through the GUI, with no custom M code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkmt0je0VNJRMlSK1YlWykrM0zcyAHH1jE3AImmpZTARE2OwSG5iEZpIYhK6SG5iJppIVmkehkgOTMQUYkx6PqpAcWoJqkB+KZpAXn4ZqgDIPwiBWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"price", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"price"}, {"Added Index1.price"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Added Index1",{{"Index", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Added Index1.price", "Price from previous row"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "variation", each ([price] - [Price from previous row]) / [Price from previous row]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"variation", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "100 base", each if [Price from previous row] is null then 100 else ([Price from previous row] * 100 ) * ( 1 + [variation] )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"month", "price", "variation", "100 base"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Other Columns",{{"variation", Percentage.Type}, {"100 base", Int64.Type}})
in
    #"Changed Type2"

-Steve

[Anonymous]

Very well explained. Thank you so much!!!