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

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?

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

Hi tmears,

 

You can change the third parameter of DATEDIFF to whatever you want. For example, HOUR, MINUTE, SECOND. 

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], MINUTE)

Best Regards,

Dale

  • tmears's avatar
    tmears
    Helper III
    8 years ago

    i have the following:

     

    Measure6 =
    VAR previousTime =
        CALCULATE (
            MIN ( 'msdyn_audithistories'[createdon]),
            FILTER (
                ALLEXCEPT ( msdyn_audithistories, msdyn_audithistories[msdyn_entityid] ),
                'msdyn_audithistories'[Index]
                    = MIN ( 'msdyn_audithistories'[Index] ) - 1
            )
        )
    VAR timeCost =
        DATEDIFF ( previousTime, MIN ( 'msdyn_audithistories'[createdon] ), SECOND )RETURN
        IF (
            timeCost = 0,
            0,
            INT ( timeCost ))

     

    But get the following, as you will see the total is 0, also if i put them in a seperate table i get minus figures.   Sorry might be being slow, your help is reallyt appriaitced 

    • 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