Forum Discussion

lubosst's avatar
lubosst
Frequent Visitor
7 years ago
Solved

Complicated date interval

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

Need Help
  • lubosst's avatar
    lubosst
    7 years ago

    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)

5 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    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's avatar
      lubosst
      Frequent Visitor
      7 years ago

      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.

  • lubosst's avatar
    lubosst
    Frequent Visitor
    7 years ago

    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)