time between - different rows

I wonder if any one could help. I am trying to calculate the time period a service activity (case) is in a certain state. however the problem is that although there is a created timestamp there is ...

Need Help

v-jiascu-msft

8 years ago

Hi tmears,

I rethought your data and found all the dates of one case id follow the time line. So there is more simple solution.

1. In the Query Editor, sort the "Case ID", then sort the "createdon".

2. Add an index.

3. Create a measure like below.

Measure 2 =
VAR previousTime =
    CALCULATE (
        MIN ( 'Table1'[createdon] ),
        FILTER (
            ALLEXCEPT ( Table1, 'Table1'[Case ID] ),
            'Table1'[Index]
                = MIN ( 'Table1'[Index] ) - 1
        )
    )
VAR timeCost =
    DATEDIFF ( previousTime, MIN ( 'Table1'[createdon] ), SECOND )
RETURN
    IF (
        timeCost = 0,
        0,
        INT ( timeCost / 3600 )
            & " hours "
            & INT ( MOD ( timeCost, 3600 ) / 60 )
            & " minutes "
            & MOD ( MOD ( timeCost, 3600 ), 60 )
            & " seconds"
    )

OR, a column like below.

Column =
VAR index = [Index]
VAR previousTime =
    CALCULATE (
        MIN ( 'Table1'[createdon] ),
        FILTER ( ALLEXCEPT ( Table1, 'Table1'[Case ID] ), 'Table1'[Index] = index - 1 )
    )
VAR timeCost =
    DATEDIFF ( previousTime, 'Table1'[createdon], SECOND )
RETURN
    IF (
        timeCost = 0,
        "0",
        INT ( timeCost / 3600 )
            & " hours "
            & INT ( MOD ( timeCost, 3600 ) / 60 )
            & " minutes "
            & MOD ( MOD ( timeCost, 3600 ), 60 )
            & " seconds"
    )

Is this the result you wanted? Please check out the demo in the attachment. The old will be deleted.

Best Regards,

Dale

time between - different rows.pbix49 KB

v-jiascu-msft

Microsoft Employee

8 years ago

Hi tmears,

You can try a measure like below. Please check out the demo in the attachment.

Measure =
VAR newTime =
    CALCULATE (
        MIN ( Table1[createdon] ),
        FILTER (
            ALLEXCEPT ( 'Table1', Table1[Case ID] ),
            Table1[msdyn_newvalue] = "New"
        )
    )
VAR inProgressTime =
    CALCULATE (
        MIN ( Table1[createdon] ),
        FILTER (
            ALLEXCEPT ( 'Table1', Table1[Case ID] ),
            Table1[msdyn_newvalue] = "In Progress"
        )
    )
VAR solvedTime =
    CALCULATE (
        MIN ( Table1[createdon] ),
        FILTER (
            ALLEXCEPT ( 'Table1', Table1[Case ID] ),
            Table1[msdyn_newvalue] = "Problem Solved"
        )
    )
VAR timeCost =
    IF (
        MIN ( Table1[msdyn_newvalue] ) = "New",
        0,
        IF (
            MIN ( 'Table1'[msdyn_newvalue] ) = "In Progress",
            DATEDIFF ( newTime, inProgressTime, SECOND ),
            IF (
                MIN ( 'Table1'[msdyn_newvalue] ) = "Problem Solved",
                DATEDIFF ( inProgressTime, solvedTime, SECOND ),
                999999
            )
        )
    )
RETURN
    IF (
        timeCost = 0,
        0,
        INT ( timeCost / 3600 )
            & " hours "
            & INT ( MOD ( timeCost, 3600 ) / 60 )
            & " minutes "
            & MOD ( MOD ( timeCost, 3600 ), 60 )
            & " seconds"
    )

Best Regards,

Dale

tmears
Helper III
8 years ago
Dale
Thank you for your help with this one. I have done the following but it not quiet getting the results i imagained. not sure if it is possible as cases can go back and foirward on status' and there a few more than i first thought.

Measure1 =
VAR newTime =
    CALCULATE (
        MIN ( Table1[createdon] ),
        FILTER (
            ALLEXCEPT ( 'Table1', Table1[Case ID] ),
            Table1[msdyn_newvalue] = "New"
        )
    )
VAR inProgressTime =
    CALCULATE (
        MIN ( Table1[createdon] ),
        FILTER (
            ALLEXCEPT ( 'Table1', Table1[Case ID] ),
            Table1[msdyn_newvalue] = "In Progress"
        )
    )
VAR solvedTime =
    CALCULATE (
        MIN ( Table1[createdon] ),
        FILTER (
            ALLEXCEPT ( 'Table1', Table1[Case ID] ),
            Table1[msdyn_newvalue] = "Problem Solved"
        )
    )
VAR timeCost =
    IF (
        MIN ( Table1[msdyn_newvalue] ) = "New",
        0,
        IF (
            MIN ( 'Table1'[msdyn_newvalue] ) = "In Progress",
            DATEDIFF ( newTime, inProgressTime, SECOND ),
            IF (
                MIN ( 'Table1'[msdyn_newvalue] ) = "Problem Solved",
                DATEDIFF ( inProgressTime, solvedTime, SECOND ),
                999999
            )
        )
    )
RETURN
    IF (
        timeCost = 0,
        0,
        INT ( timeCost / 3600 )
            & " hours "
            & INT ( MOD ( timeCost, 3600 ) / 60 )
            & " minutes "
            & MOD ( MOD ( timeCost, 3600 ), 60 )
            & " seconds"
    )
- v-jiascu-msft
  Microsoft Employee
  8 years ago
  Hi tmears,
  
  I rethought your data and found all the dates of one case id follow the time line. So there is more simple solution.
  
  1. In the Query Editor, sort the "Case ID", then sort the "createdon".
  
  2. Add an index.
  
  3. Create a measure like below.
  
  Measure 2 = VAR previousTime = CALCULATE ( MIN ( 'Table1'[createdon] ), FILTER ( ALLEXCEPT ( Table1, 'Table1'[Case ID] ), 'Table1'[Index] = MIN ( 'Table1'[Index] ) - 1 ) ) VAR timeCost = DATEDIFF ( previousTime, MIN ( 'Table1'[createdon] ), SECOND ) RETURN IF ( timeCost = 0, 0, INT ( timeCost / 3600 ) & " hours " & INT ( MOD ( timeCost, 3600 ) / 60 ) & " minutes " & MOD ( MOD ( timeCost, 3600 ), 60 ) & " seconds" )
  
  OR, a column like below.
  
  Column = VAR index = [Index] VAR previousTime = CALCULATE ( MIN ( 'Table1'[createdon] ), FILTER ( ALLEXCEPT ( Table1, 'Table1'[Case ID] ), 'Table1'[Index] = index - 1 ) ) VAR timeCost = DATEDIFF ( previousTime, 'Table1'[createdon], SECOND ) RETURN IF ( timeCost = 0, "0", INT ( timeCost / 3600 ) & " hours " & INT ( MOD ( timeCost, 3600 ) / 60 ) & " minutes " & MOD ( MOD ( timeCost, 3600 ), 60 ) & " seconds" )
  
  Is this the result you wanted? Please check out the demo in the attachment. The old will be deleted.
  
  Best Regards,
  
  Dale
  time between - different rows.pbix49 KB
  - tmears
    Helper III
    8 years ago
    thank you dso umch for this. One final question, is there a way just to return minutes, so i can total and average the duration?

Forum Discussion

time between - different rows

Related content

formatting time differences

Calculating day/time difference

Time difference

Power BI Scheduled Refresh – Different Timings for Specific Days

How to avoid people creating multiple times the same table in different workspaces