Change date history table

Hi Anonymous ,

First, please add a index column in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xdFBCsAgDATAv3gW3GisyVuk//9G9VAUxKKU0ltgYNkkORsy1kSIJAQuo4eHE0dsTruEiigkDT2+xHAXYj2GQgtYYhVpEluRqEP6AdMO9quEeSzUYU9bcFV5rdx0fCo/nWmO5wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkItemId = _t, BoardLocationSK = _t, ChangedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkItemId", Int64.Type}, {"BoardLocationSK", Int64.Type}, {"ChangedDate", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

Then create a measure as below:

Measure = 
VAR _index =
    MAX ( 'Table'[Index] )
VAR _cdate =
    CALCULATE (
        MAX ( 'Table'[ChangedDate] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[WorkItemId] = MAX ( 'Table'[WorkItemId] )
                && 'Table'[Index] < _index
        )
    )
RETURN
    IF ( ISBLANK ( _cdate ), MAX ( 'Table'[ChangedDate] ), _cdate )​

Or you can update the formula of calculated column "PreviousWrong" as below after the index column be added:

PreviousWrong = 
COALESCE (
    MAXX (
        FILTER (
            'Table',
            'Table'[WorkItemId] = EARLIER ( 'Table'[WorkItemId] )
                && 'Table'[Index] < EARLIER ( 'Table'[Index] )
        ),
        'Table'[ChangedDate]
    ),
    'Table'[ChangedDate]
)

Best Regards

Rena