cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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
2 ACCEPTED SOLUTIONS
Super User

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/

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Solution Specialist

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}}),
#"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

3 REPLIES 3
Super User

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/

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Solution Specialist

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}}),
#"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
Not applicable

Very well explained. Thank you so much!!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.