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

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