Help needed for a calculation

[andi2333]

Hello. I have a report (xls) which is usually updated on workdays with orders which are stuck in our supply chain systems.

Generally, when an order appears on a certain day in that report, it counts as stuck for one day. If it appears on two days (not necessarily consecutive days), it counts as two days stuck etc.

Format example

Report date   OrderNumber

01.01.2024     ABC123

01.01.2024     ABC124

02.01.2024     ABC123

03.01.2024     ABC125

Now, I would like to calculate the AVERAGE duration of orders being stuck over time. My problem here is that the report is only issued on workdays, however I would like to factor in "calendar time", meaning if an order is stuck on Friday and the following Monday, it should count also the weekend (+2 days). Same for public holidays (where no reports are issued).

Is there a way to solve this?

Thanks

[v-jingzhan-msft]

Hi @andi2333 

You can first calculate the duration between the first report date and last report date of each order number, then calcualte the average of these durations. Try creating the following two measures:

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

Avg Duration = 
DIVIDE(
    SUMX(VALUES('Table'[OrderNumber]),[Stuck Duration]), 
    DISTINCTCOUNT('Table'[OrderNumber])
)

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[andi2333]

Hi Jin, thanks, I am not entirely sure how I calculate earliest and last report date for each order? I do not capture this by default in the report. Obviously, when the order appears first time in the report, that report date should be the "earliest date"...but I am not entirely clear how to come to the "last date"?

Thanks

[v-jingzhan-msft]

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

[BA_Pete]

Hi @andi2333 ,

You said that the days orders can appear may not be consecutive. How should these be handled? Do you just want to take the earliest and latest dates the order appears in the data and get total calendar days between them?

Pete

[andi2333]

Hello Pete, yes, that would be the intention. 

[BA_Pete]

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