Forum Discussion

andi2333's avatar
andi2333
Helper I
2 years ago
Solved

Help needed for a calculation

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

Need Help
  • BA_Pete's avatar
    BA_Pete
    2 years ago

     

    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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

6 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    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's avatar
      andi2333
      Helper I
      2 years ago

      Hello Pete, yes, that would be the intention. 

      • BA_Pete's avatar
        BA_Pete
        Super User
        2 years ago

         

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      andi2333
      Helper I
      2 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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