Need date differences by status change when status changes

[Ryan0096]

We have work IDs that can go through status 0-10  and it is not uncommon for it to go forward and backwards.  Trying to get the date changes between the sequences so min when it ws in status x and min first time status changes and so on. 

Quick note the dates are not always sequencal, it can go into status 2 on 1/10 then no other entry until the next status change (up or down) days or weeks later. 

Thank you.

* Updated table - Our status can have multiple entries before a status change basically a notes field (not shown).  Also our status can go forward and backward based on revisions and budget reasons.  I am trying to flag when for any ID status changes from the previous status then to get the total days it was in that status.  In the update table we can see status 2 is used 3 different times for 34+13+6 = 53 days total.   The end goal is to be able to summarize all of our work IDs and get an average age in each status. 

[dufoq3]

Hi @Ryan0096, what about this?

For last row there is no end date so it calculates today minus 7th of April 2024 which is 32 days.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBLCgAxCAPQu7gulETnd5bS+19jqN1VXbh5hEAcQ0C1S5pgXUenyWwnPydzp63wL3femTPWwxmFVzV8M9ce4nAGc2e61uJv1Hmn5w8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Date = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", Int64.Type}, {"Date", type date}}, "en-US"),
    GroupedRows = Table.Group(ChangedType, {"ID", "Status"},
        {{"ChangeDate", each [Date]{0}, type date}},
        GroupKind.Local,
        (s,c)=> Byte.From( (s[ID] = c[ID] and s[Status] <> c[Status]) or s[ID] <> c[ID] ) ),
    Fn_TotalDays = (myTable as table)=>
        [ a = List.Buffer(myTable[ChangeDate]),
          lg = 
            List.Generate(
                ()=> [ x = 0, y = Duration.TotalDays(a{x+1} - a{x}) ],
                each [x] < List.Count(a),
                each [ x = [x]+1, y = Duration.TotalDays((try a{x+1} otherwise Date.From(DateTime.FixedLocalNow())) - a{x}) ],
                each [y]
            ),
        toTable = Table.FromColumns(Table.ToColumns(myTable) & {lg}, Value.Type(myTable & #table(type table[Total Days=Int64.Type],{})))
        ][toTable],
    GroupedRows2 = Table.Group(GroupedRows, {"ID"}, {{"All", each 
        [ a = Fn_TotalDays(_),
          b = Table.Group(a, {"ID", "Status"}, {{"Total Days", (x)=> List.Sum(x[Total Days]), Int64.Type}})
        ][b], type table}}),
    CombinedAll = Table.Combine(GroupedRows2[All])
in
    CombinedAll

[Ryan0096]

Update orginal post

[ronrsnfld · Accepted solution]

Your updated example makes more sense, except for the missing Datediff entry on row 4 which I assume is a typo.  Try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", Int64.Type}, {"Date", type date}}),

//Note use of GroupKind.Local
//Add a column showing the minimum date for each status group
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Status"}, {
        {"all", (t)=> Table.AddColumn(t, "Min Date", each List.Min(t[Date])),     
        type table [ID=nullable number, Status=nullable number, Date=nullable date, Min Date = nullable date]}
        }, GroupKind.Local),

    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Date", "Min Date"}),

//Shift the min date column for easier comparisons
    #"Shifted Min Date" = Table.FromColumns(
        Table.ToColumns(#"Expanded all") &
        {{null} & List.RemoveLastN(#"Expanded all"[Min Date])},
        type table [ID=nullable number, Status=nullable number, Date=nullable date, Min Date = nullable date, Shifted Min = nullable date]
        ),

//Add the Datediff column at the change in status position
    #"Add Need"= Table.AddColumn(#"Shifted Min Date", "Datediff on Change", 
            each if Duration.Days([Min Date] - [Shifted Min])= 0 then null else Duration.Days([Min Date] - [Shifted Min]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Add Need",{"Shifted Min", "ID", "Min Date"})
in
    #"Removed Columns"

[ronrsnfld]

I don't understand your entries in Need for row 11 to row 14

11. No change in status c/w 10 but Need = 1
12. Status changeds from 2 to 1 but Need = null
13. No change in status but Need = 2
14. Status change, but min date of previous status is 2 days earlier, Need = 1

[Ryan0096]

Another way to put it, I need the date difference from the first date the status changes vs the first date of the new status change per ID and in sequence for when the status will go forward and backward.  In row 11,14 and 15 it only stayed in the prior status for 1 day.   In row 8 the status changed that day to status 1 but it was in status 2 for 4 days.