Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
tmears
Helper III
Helper III

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

 

 staus flow.PNG

1 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".

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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

time_between_different_rows

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

 

 

Capture.PNG

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 

Capture11.PNGCapture12.PNG

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

My pleasure.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.