Forum Discussion
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
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
13 Replies
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
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"
)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
