Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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
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
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
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
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
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
What a man! Dan thanks so much, you really are a start!!
My pleasure.
Best Regards,
Dale
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |