subtract dates in the same rows

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYzN7UwMTIzUdJRCnYLAZKm+ob6RgZGRkqxOqjSQWHhEGlj7PJw7eY49Ls6guWNTDDlTZHNx2K/KbL5pjj0w8w3wJQ3QzLfCLs0zHiY72IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equip_Num = _t, Status = _t, #"Completed On" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Completed On", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Equip_Num", Order.Ascending}, {"Completed On", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date_Difference", each try if [Equip_Num]=#"Added Index"{[Index]-1}[Equip_Num]
and [Status]="REA"
and #"Added Index"{[Index]-1}[Status] ="SFT"
then  [Completed On]-#"Added Index"{[Index]-1}[Completed On]
else null otherwise null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Date_Difference", Int64.Type}})
in
    #"Changed Type1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

Hi NPC,

You may create a Measure as well.

Date Difference =
VAR Prev_SFT_Date =
    CALCULATE (
        MAX ( 'Table'[Completed On] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Equip_Num] ),
            'Table'[Completed On] < MAX ( 'Table'[Completed On] )
                && 'Table'[Status] = "SFT"
                && MAX ( 'Table'[Status] ) = "REA"
        )
    )
VAR Prev_Date =
    CALCULATE (
        MAX ( 'Table'[Completed On] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Equip_Num] ),
            'Table'[Completed On] < MAX ( 'Table'[Completed On] )
        )
    )
VAR Diff =
    CALCULATE (
        DATEDIFF ( Prev_SFT_Date, MAX ( 'Table'[Completed On] ), DAY ),
        FILTER ( 'Table', Prev_SFT_Date = Prev_Date )
    )
RETURN
    IF ( ISBLANK ( Diff ), "", Diff )

Then, the result looks like this.

Also, attached the pbix as reference.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun