IDENTIFY THE MOMENT OF VALUE CHANGE

[FieldsG]

From the series of columns identify the first time I change the value, resulting in the header name of that column.
And in another column identify the last value before the change, also with the name of the header of that column.

But with a power query formula, not in DAX

[dufoq3 · Accepted solution]

Hi @FieldsG,

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwNzQwNDAwMjAyMjMAAiUdJUsqYUND3ERsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Long OP" = _t, #"2022-01" = _t, #"2022-02" = _t, #"2022-03" = _t, #"2022-04" = _t, #"2022-05" = _t, #"2022-06" = _t, #"2022-07" = _t, #"2022-08" = _t, #"2022-09" = _t, #"2022-10" = _t, #"2022-11" = _t, #"2022-12" = _t, #"2023-01" = _t, #"2023-02" = _t, #"2023-03" = _t, #"2023-04" = _t, #"2023-05" = _t, #"2023-06" = _t, #"2023-07" = _t, #"2023-08" = _t, #"2023-09" = _t, #"2023-10" = _t, #"2023-11" = _t, #"2023-12" = _t]),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(Source, {"Long OP"}, "Year Month", "Value"),
    AddedIndex = Table.AddIndexColumn(UnpivotedOtherColumns, "Index", 0, 1, Int64.Type),
    Ad_MomentOfChange = Table.AddColumn(AddedIndex, "Moment of Change", each try if [Value] = AddedIndex{[Index]-1}[Value] then null else [Year Month] otherwise null, type text),
    #"Added Custom" = Table.AddColumn(Ad_MomentOfChange, "Last Value Before Change", each if [Moment of Change] = null then null else Ad_MomentOfChange{[Index]-1}[Value], type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Year Month"]), "Year Month", "Value"),
    #"Filled Up" = Table.FillDown(#"Pivoted Column", Table.ColumnNames(#"Pivoted Column")),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Moment of Change] = "2023-07"))
in
    #"Filtered Rows"

[Greg_Deckler]

@FieldsG Well, in either DAX or PQ, you likely want to unpivot your columns. Then maybe sort it and add an index and then you can grab the difference between the previous row and the current row. Then you just need to get the min date (Attribute) where the difference is not 0.

If you give up on a PQ solution, here is the DAX way to do it: https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211