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

[xarius]

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.

[MFelix · Accepted solution]

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