Forum Discussion

tmears's avatar
tmears
Helper III
8 years ago
Solved

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's avatar
    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".

    time_between_different_rows2

    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"
    )

    time_between_different_rows3

     

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

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi tmears,

 

I'm afraid you can't use it like the second snapshot showed. For example, one value of the "msdyn", the "New", could have many createOn time. We need calculate it one id by one id, then sum them up.

Did you verify the result of the first snapshot? Are the answers right?

 

Best Regards,

Dale

tmears's avatar
tmears
Helper III
8 years ago

Dale

 

Yes the calcualtions seems to work perfectly, thank you.  I just would like to understand the average time across the business for cases in each state, so i can cvalcualte trends etc

 

Many thanks


Tim 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Tim,

     

    I would suggest you use a calculated column. That would be easy to do aggregation.

    Column 2 =
VAR index = [Index]
VAR previousTime =
    CALCULATE (
        MIN ( 'Table1'[createdon] ),
        FILTER ( ALLEXCEPT ( Table1, 'Table1'[Case ID] ), 'Table1'[Index] = index - 1 )
    )
RETURN
    DATEDIFF ( previousTime, 'Table1'[createdon], MINUTE )

    time_between_different_rows4

     

    Best Regards,

    Dale

  • tmears's avatar
    tmears
    Helper III
    8 years ago

    What a man! Dan thanks so much, you really are a start!!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    My pleasure.

     

    Best Regards,

    Dale

  • tmears's avatar
    tmears
    Helper III
    8 years ago

    One last question, do you have any ideas/thoughts on how i could calcuate working hours, in the past i have created an firstdayendtime and end time of the last day but i have always had these two dtaes/time, but in this case i only have the one time on a row 

    not sure this is going to be possible though

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    That's possible. But there are many things to consider. For example, the holidays, start time and end time aren't in one day, intervals during a workday, etc. A new Date table is needed. I would suggest you open a new thread in this forum. 

     

    Best Regards,

    Dale