Complicated date interval

[lubosst]

Hi, I have problem with calculating date interval (cycle time) in my table.

In the table, there are records for order changes - there may be multiple rows for one order:

I need to calculate time, each order spent in green(field_value_new) between two dates - cycle time for different teams. Tricky part is, that handover between teams may be anytime - I explain below.

I was able to calculate next changed time for each order:

And now, what I actualy need.

let's take for example order 53826.

Team1 gets the order 2/11/2019 12:00:00 AM and finish their work at 2/14/2019 01:30:00 PM.

Team2 gets the order 2/14/2019 01:30:00 PM and finish their work at 5/29/2019 12:00:00 AM.

...

And I need to calculate their "green" time. Any ideas?

Thanks

[lubosst · Accepted solution]

Hi, I was able to find solution, so maybe it will help somebody...

At first, code for next_changed_date:

next_changed_date = 
VAR currentDate = 'Table'[changed_date]
VAR currentID = 'Table'[id]
VAR nextDate =
    CALCULATE (
        MIN ( 'Table'[changed_date] ),
        FILTER (
            ALL ( 'Table' ),
            AND (
                'Table'[id] = currentID,
                'Table'[changed_date] > currentDate
            )
        )
    )
RETURN
    IF ( nextDate > 0, nextDate, NOW () )

Now I have all changes with starting and ending date (in case, that there are no ending, I fill NOW() date).

Then I have created another column for each team, which return changed date, but with only dates after their starting date:

changed_date_Issued_To_Team1 = 
IF (
    'Table'[changed_date] < RELATED ( 'Team Table'[Issued_To_Team1] ),
    IF (
        RELATED ( 'Team Table'[Issued_To_Team1] ) > 'Table'[next_changed_date],
        BLANK (),
        RELATED ( 'Team Table'[Issued_To_Team1] )
    ),
    IF (
        RELATED ( 'Team Table'[Issued_To_Team1] ) = 0,
        BLANK (),
        'Table'[changed_date]
    )
)

And create another column with next_changed_date for each team - with only dates before team ends (ignoring other dates):

next_changed_date_End_Team1 = 
IF (
    'Table'[changed_date_Issued_To_Team1] = 0,
    BLANK (),
    IF (
        RELATED ( 'Team Table'[end_team1] ) <> 0,
        IF (
            'Table'[next_changed_date] < RELATED ( 'Team Table'[end_team1] ),
            'Table'[next_changed_date],
            IF (
                RELATED ( 'Team Table'[end_team1] ) < 'Table'[changed_date_Issued_To_Team1],
                BLANK(),
                RELATED ( 'Team Table'[end_team1] )
            )
        ),
        IF (
            'Table'[next_changed_date] <> 0,
            'Table'[next_changed_date],
            NOW ()
        )
    )
)

And finaly CT for each team:

CT_Team1 = 
IF (
    'Table'[next_changed_date_End_Team1] > 0,
    'Table'[next_changed_date_End_Team1] - 'Table'[changed_date_Issued_To_Team1]
)

Now I have created measure, which calculates sum of CT:

CT_green_Team1 = 
CALCULATE (
    SUM ( 'Team'[CT_Team1] ),
    FILTER (
        'Team',
        'Team'[field_value_new] = "green"
    )
)

(Except tha last one, all are calculated columns)

[Mariusz]

Hi @lubosst 

Can you please explain you examples
Why Team1 order 2/11/2019 12:00:00 AM and finish their work at 2/14/2019 01:30:00 PM?

Thanks

Mariusz

[lubosst]

Hi, that's that tricky part.

There are multiple teams working on order simultaneously. The color means: green-everything OK; amber-something might be wrong; red-there is issue, order is on hold ... that's the table about.

Than, I have in another table multiple columns, that tell me, when each team starts and ends.

So order can have green color while one team ends their work and another begins. This is true about amber color too.

Here is actual data for one order:

First table shows team dates - T1 ends when T2 begins

Second table shows changes in color for order.

Hope this helps.

[Mariusz]

Hi @lubosst 

Can you provide the data sample that can copied?

Thanks

Mariusz

[lubosst]

Hi, hope this will work for you:

raw csv file