time between - different rows

[tmears]

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 no end timestamp.  there is only a created on timestamp of the next stage of the flow:  Hard to explain but will try.  

Our service activites go through the following stages:

1 new

2 In Progress

3 Problem Solved

therefore the end date timestamp for "New" would be the created on date/timestamp of "In Progress" therfore using the example in the picture would be 13:13:51 to 15:31:22 therfore 2hrs 18 mins.  But not sure this is even possible through DAX or if anyone has any suggestions it would be really appriciated 

 

[v-jiascu-msft]

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]

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 · Accepted solution]

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

[tmears]

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]

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]

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]

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]

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]

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 )

Best Regards,

Dale

[tmears]

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

[v-jiascu-msft]

My pleasure.

Best Regards,

Dale

[tmears]

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]

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