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...
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

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