Forum Discussion
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 ...
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
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 )
Best Regards,
Dale