Date Duration BY Order

[Anonymous]

Hi Everyone,

Please help me to resolve this query.

I have 2 tables. 1st table contains production data and 2nd table contain the Production team data.

What I want to do is for for all the Order Numbers in Producton_Line table calculate the number of total hours spend by each team.

Team data is available in the Team operation data.

The logic will be each order in Production data table contains Team name Reference column.for each team in team reference in production data. you need to go and take the sum of all the line items from Teams Operations data. 
But the tricky thing  is you need to consider  minimum starting time and Maximum complete time for each order when you take times. Also you have to remove any other orders completed during same time by same team.

Hope this is clear.

Here is the link to the file and I have provided detailed information inside the file itself.  Thank you.

https://easyupload.io/1au7xq

Here is my current code.

Answer = 
var __selserialnumber = 
    MAX(ProductionLinesTasks[Serial_Number])
var __selteam = 
    MAX(ProductionLinesTasks[Team_Name])
var __minstarttime = 
    CALCULATE(
        MIN(ProductionLinesTasks[Trans_DateTime]),
        ProductionLinesTasks[Action] = "Start"
    )
var __maxendtime = 
    CALCULATE(
        MAX(ProductionLinesTasks[Trans_DateTime]),
        ProductionLinesTasks[Action] = "Complete"
    )
var __answer = 
    FILTER(
        Teams_Operators,
        Teams_Operators[Team_Name] = __selteam &&
        Teams_Operators[Join_DateTime] >= __minstarttime &&
        Teams_Operators[Leave_DateTime] <= __maxendtime
    )
        
return
    SUMX(
        __answer,
        Teams_Operators[Duration]
    )

here is my Current code. 

[smpa01]

@Anonymous  I have loked into this data and few things are not making sense

| Order_Number | Team_Name | Trans_DateTime          | Action   | Explanation                           |
|--------------|-----------|-------------------------|----------|---------------------------------------|
| K00000001    | Team 4    | 2021-10-21 8:00:00 AM   | Start    | Min Start by Order_NumberTeam_Name    |
| K00000001    | Team 4    | 2021-10-21   8:00:00 AM | Complete | Max Complete by Order_NumberTeam_Name |
| K00000001    | Team 1    | 2021-10-23   8:00:00 AM | Start    | Min Start by Order_NumberTeam_Name    |
| K00000001    | Team 1    | 2021-10-23   8:00:00 AM | Complete | Max Complete by Order_NumberTeam_Name |
| K00000001    | Team 2    | 2021-10-25   6:35:55 AM | Complete | Min Complete by Order_NumberTeam_Name |
| K00000001    | Team 3    | 2021-10-23   8:00:00 AM | Complete | Max Complete by Order_NumberTeam_Name |
| K00000002    | Team 1    | 2021-10-24   5:56:13 AM | Start    | Min Start by Order_NumberTeam_Name    |
| K00000002    | Team 1    | 2021-10-24   6:06:13 AM | Complete | Max Complete by Order_NumberTeam_Name |
| K00000003    | Team 1    | 2021-10-25   6:51:11 AM | Start    | Min Start by Order_NumberTeam_Name    |
| K00000003    | Team 1    | 2021-10-25   7:18:25 AM | Complete | Max Complete by Order_NumberTeam_Name |
| K00000003    | Team 3    | 2021-10-25 4:40:22 AM   | Start    | Min Start by Order_NumberTeam_Name    |
| K00000003    | Team 2    | 2021-10-25 5:55:37 AM   | Complete | Max Complete by Order_NumberTeam_Name |
| K00000002    | Team 2    | 2021-10-25 6:35:32 AM   | Complete | Min Complete by Order_NumberTeam_Name |
| F00000392    | Team 3    | 2021-10-24 1:59:27 AM   | Start    | Max Start by Order_NumberTeam_Name    |
| F00000392    | Team 3    | 2021-10-25   4:18:08 AM | Complete | Min Complete by Order_NumberTeam_Name |
| K00000004    | Team 1    | 2021-10-26 9:16:08 AM   | Start    | Max Start by Order_NumberTeam_Name    |

 The above is the analysis that I ran to see

what is the Min Trans_DateTime, Max Trans_date_Time

by Order_Number, Team_Name, Action respectively when Action=Start and Action=Complete

The ask was following

"The logic will be each order in Production data table contains Team name Reference column.for each team in team reference in production data. you need to go and take the sum of all the line items from Teams Operations data. 
But the tricky thing  is you need to consider  minimum starting time and Maximum complete time for each order when you take times. Also you have to remove any other orders completed during same time by same team."

If I refer to the above table K00000001 was started and completed by Team 4 and Team 1 and only Completed by Team 2 and Team 3.

Also you have to remove any other orders completed during same time by same team - How does it apply this to K00000001?

But the tricky thing  is you need to consider  minimum starting time and Maximum complete time for each order when you take times - If I take the Min Start and Max Complete by ignoring the Team_Name Partition it will result irespetively in 10/21/2021 8:00:00 AM (lowest Start) and 10/25/2021 6:35:55 AM (highest Complete) which does not relate to any Team.

Can you please improve the quesytion by clarifying the logic and by clearly displaying the desired output.

[Anonymous]

@Greg_Deckler @Jihwan_Kim Please help

[Anonymous]

@Fowmy @amithchandak