Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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. 

 

Lucaswolff_0-1643313726608.png

 

I appreciate if someone could help me.

 

 

monthpricevariation100 base
dez/191-100
jan/201,3434%134
fev/201,437%143
mar/201,430%143
abr/201,430%143
mai/201,430%143
jun/201,430%143
jul/201,55%150
ago/201,50%150
set/201,50%150
out/201,50%150
nov/201,50%150
dez/201,50%150
2 ACCEPTED SOLUTIONS
MFelix
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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:

SteveHailey_0-1643637900561.png

and ended with this:

SteveHailey_1-1643637919176.png

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

SteveHailey_2-1643637943310.png

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

View solution in original post

3 REPLIES 3
MFelix
Super User
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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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:

SteveHailey_0-1643637900561.png

and ended with this:

SteveHailey_1-1643637919176.png

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

SteveHailey_2-1643637943310.png

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
Not applicable

Very well explained. Thank you so much!!! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors