cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
xarius Regular Visitor
Regular Visitor

Calculated column with previous row referencing same calculated column (DAX or Power M)

Hi,

 

I'm trying to create a new column in my table that relies on data from another column in the same row and a previous row from the row I'm trying to calculate.

 

My understanding is that DAX can't do it (from everything I've read you can't reference a previous row in the column you're trying to calculate - although very happy to be corrected on this), but that Power M might be able to via List. Accumulate?

 

I have almost no experience with Power M so any help would be helpful. I've posted an image of what I'm trying to achieve below.

 

image.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

HI @xarius ,

 

You need to do the following:

  • Filter out all the rows with Start
  • Add a new index column starting in 1
  • Merge the last step before the filter with the new step
  • Do a fill down on the period column.

Check M code below and attach PBIX:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JLCpRitWJVnJEIp3ApDMS6YJEuoJJnDpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Label = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Label] = "Start")),
    #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Period", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Added Index1",{"Index"},"Added Index",JoinKind.FullOuter),
    #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Period"}, {"Period"}),
    #"Filled Down" = Table.FillDown(#"Expanded Added Index",{"Period"})
in
    #"Filled Down"

Regards,

MFelix



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

Proud to be a Datanaut!




Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Oh, sorry - just recognized the problem now.

Yes, you have to perform some recursice operation here:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqUYrViVYyAvIcwSxjOMsEyHICs0yBLGcwywzOMgeyXMAsCzjLEshyBbMMDVCMNzSEm2qIsMrQGGJDLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Index = _t, Label = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Label", type text}}),
    GenerateList = 
        List.Skip(
        List.Generate(
            ()=> [Result = 0, Counter = 0],
            each [Counter] <= Table.RowCount(#"Changed Type"),
            each [
                    Switch = [
                    Start = 0,
                    A = [Result] + 1,
                    B = [Result] + 2,
                    C = [Result] + 3
                    ],
                    Result = try Record.Field(Switch, #"Changed Type"[Label]{[Counter]}) otherwise [Result]-1,
                Counter = [Counter]+1
            ],
            each [Result])
            ,1),
    MergeColumns = Table.FromColumns(Table.ToColumns(Source) & {GenerateList}, Table.ColumnNames(Source) & {"ExpectedOutput"})
in
    MergeColumns

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




14 REPLIES 14
Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

HI @xarius ,

 

You need to do the following:

  • Filter out all the rows with Start
  • Add a new index column starting in 1
  • Merge the last step before the filter with the new step
  • Do a fill down on the period column.

Check M code below and attach PBIX:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JLCpRitWJVnJEIp3ApDMS6YJEuoJJnDpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Label = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Label", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Label] = "Start")),
    #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Period", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Added Index1",{"Index"},"Added Index",JoinKind.FullOuter),
    #"Expanded Added Index" = Table.ExpandTableColumn(#"Merged Queries", "Added Index", {"Period"}, {"Period"}),
    #"Filled Down" = Table.FillDown(#"Expanded Added Index",{"Period"})
in
    #"Filled Down"

Regards,

MFelix



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

Proud to be a Datanaut!




xarius Regular Visitor
Regular Visitor

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Hi @MFelix,

 

Thank you for this, it will definitely be useful but I also maybe made my use case too simple as I was hoping to apply the same logic to other columns with a bit more of a complex calculation.

 

I still need to definitely reference a row before the one currently being calculated, please see my example below:

 

image.png

xarius Regular Visitor
Regular Visitor

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Just bumping this in the hope someone can help me with the second half of my problem! (@MFelix)

Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Hi @ImkeF ,

 

Can you help out on this request?

 

Regards,

MFelix



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

Proud to be a Datanaut!




Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

I've written a function to fetch the previous row fast on large datasets here:

 

https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-po...

 

Pls let me know if you have problems implementing it.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Hi @ImkeF ,

 

I have found that post Smiley HappySmiley HappySmiley HappySmiley Happy not really sure if could be use to implement in this case,  but was suspicious of that.

 

Regards,

MFelix



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

Proud to be a Datanaut!




xarius Regular Visitor
Regular Visitor

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

Hi @ImkeF,

 

I've seen that post before too, but as far as I can tell you can only reference a previous row of a different column (one that already has data/is calculated), not the one that you are currently calculating? Is that correct?

Super User
Super User

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

No, you can use it for your use case as well: By default, the function would return all columns of the previous row.

But as stated in sample 3, you can narrow it down to just one column you're interested in:

 

image.png

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




xarius Regular Visitor
Regular Visitor

Re: Calculated column with previous row referencing same calculated column (DAX or Power M)

But in your example the "Value" column is fully formed - I need to calculate the current row of "Value" based on the previous row of "Value" - please see my 2nd screenshot for an example of what I'm trying to achieve.