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

HI Anonymous ,

 

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

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