Help needed for a calculation

No problem.

In Power Query, create a new blank query and paste this over the default code within Advanced Editor to see an example of how to do your calculation:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyMTJR0lRydnQyNjpVgdbOImEHEjHOqN0cVNIeIWWNTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report date" = _t, OrderNumber = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Report date", type date}}),

// Relevant steps from here ===>
    groupOrderNumber = Table.Group(
        chgTypes,
        {"OrderNumber"},
        {{"data", each _, type table [Report date=nullable date, OrderNumber=nullable text]}}
    ),
    addDaysStuck = Table.AddColumn(
        groupOrderNumber,
        "DaysStuck",
        each Duration.TotalDays(
            List.Max([data][Report date])
            - List.Min([data][Report date])
        ) + 1, Int64.Type
    )
    
in
    addDaysStuck

Summary of steps:

1) groupOrderNumber = Group By [OrderNumber] and use the All Rows operator for the aggregated column.

2) addDaysStuck = Get the days difference between the earliest and latest dates within the [Report date] column for each nested [OrderNumber] table.

This gives the following output:

At this point, you can either delete the [data] column that contains the nested tables, or you can reinstate any nested columns back to the table by exanding the [data] column using the button highlighted above.

Pete

Hi andi2333 

Don't worry. In my previous reply, the first measure [Stuck Duration] is to calculate the duration between the earliest and last report date. MIN('Table'[Report date]) is to get the earliest date and MAX('Table'[Report date]) is to get the last date. DATEDIFF function is to get the interval days between both. And finally add 1 to it. 

Stuck Duration = DATEDIFF(MIN('Table'[Report Date]),MAX('Table'[Report Date]),DAY)+1

The second measure will then use the first measure to calculate the duration days for every order number and calculate the average of them. You can use it directly in a visual e.g. Card visual to get the overall Avg of durations like below. I just use a table visual to show all details to help verify the result.  

Best Regards,
Jing