Forum Discussion
Subtract date/time based on text contained within a string
I have a ticketing system that audits field changes on ticket records. I am doing some research on how long it takes tickets to be raised in priority from low or medium to high or critical. I am also researching the total time it take a ticket from the time it is created to the time it reaches its final priority. Date examples with additional explaination below. My desired results are in hours rounded to 1 decimal.
My audit record table. I need to only look for the priority related changes within the "Field Changes" column (in bold).
| AuditDate | IncidentID | Field Changes |
| 2/26/2017 14:56 | INC000001771984 | Status: Assigned (1)~Status_Reason: ~Urgency: 2-High (2000)~Impact: 2-Significant/Large (2000)~Priority: High (1)~Priority Weight: 301 |
| 2/27/2017 16:44 | INC000001771984 | Status: Assigned (1)~Priority Weight: 201~Urgency: 3-Medium (3000)~Impact: 3-Moderate/Limited (3000)~Priority: Medium (2) |
| 3/2/2017 12:37 | INC000001771984 | Priority Weight: 301~Urgency: 2-High (2000)~Impact: 2-Significant/Large (2000)~Priority: High (1)~ |
| 5/15/2017 14:49 | INC000001836727 | Status: In Progress (2)~Priority Weight: 201~Impact: 3-Moderate/Limited (3000)~Priority: Medium (2) |
| 5/15/2017 15:13 | INC000001836727 | Priority Weight: 301~Urgency: 2-High (2000)~Impact: 2-Significant/Large (2000)~Priority: High (1)~ |
| 5/15/2017 15:34 | INC000001836727 | Priority Weight: 201~Urgency: 3-Medium (3000)~Impact: 3-Moderate/Limited (3000)~Priority: Medium (2)~ |
| 10/31/2017 16:57 | INC000001996080 | Priority Weight: 301~Urgency: 2-High (2000)~Impact: 2-Significant/Large (2000)~Priority: Low (3)~ |
| 11/1/2017 18:37 | INC000001996080 | Priority Weight: 201~Urgency: 3-Medium (3000)~Impact: 3-Moderate/Limited (3000)~Priority: Medium (2)~ |
| 11/1/2017 18:43 | INC000001996080 | Priority Weight: 301~Urgency: 2-High (2000)~Impact: 2-Significant/Large (2000)~Priority: High (1)~ |
| 11/12/2017 21:13 | INC000002007779 | Priority Weight: 201~Urgency: 3-Medium (3000)~Impact: 3-Moderate/Limited (3000)~Priority: Medium (2)~ |
| 11/14/2017 15:44 | INC000002007779 | Priority Weight: 301~Urgency: 2-High (2000)~Impact: 2-Significant/Large (2000)~Priority: Critical (0)~ |
| 1/3/2018 17:42 | INC000002059015 | Priority Weight: 301~Urgency: 2-High (2000)~Impact: 2-Significant/Large (2000)~Priority: Low (3)~ |
| 1/3/2018 18:56 | INC000002059015 | Priority Weight: 201~Urgency: 3-Medium (3000)~Impact: 3-Moderate/Limited (3000)~Priority: Medium (2)~ |
| 1/3/2018 19:13 | INC000002059015 | Priority Weight: 301~Urgency: 2-High (2000)~Impact: 2-Significant/Large (2000)~Priority: High (1)~ |
| 1/3/2018 20:13 | INC000002059015 | Priority Weight: 301~Urgency: 2-High (2000)~Impact: 2-Significant/Large (2000)~Priority: Critical (0)~ |
My second table already has the ticket ID listed (one record per ticket).
For the "Time to Critical/High" colum I need the time between the first record of each ticket to the first record that is high or critical in the field changes column below. If the first record of the ticket is high or critical then the time would be zero. If the ticket has 4 audit records (low, medium, high, then critical), then I would need the time between the low and high records.
For the "Total Time" column, I simply need the time between the first and last record of each ticket regardless or priority.
| IncidentID | Time to High/Critical | Total Time |
| INC000001771984 | 0 | 93.7 |
| INC000001836727 | 0.4 | 0.7 |
| INC000001996080 | 25.8 | 25.8 |
| INC000002007779 | 42.5 | 42.5 |
| INC000002059015 | 1.5 | 2.5 |
hi, Anonymous
After my research, you could do these as below:
Step1:
Use SEARCH or FIND Function to add a column that based on text contained.
contained = IF ( SEARCH ( "Priority: Critical", 'Table'[Field Changes], 1, 0 ) > 0, 0, IF ( SEARCH ( "Priority: High", 'Table'[Field Changes], 1, 0 ) > 0, 1, IF ( SEARCH ( "Priority: Medium", 'Table'[Field Changes], 1, 0 ) > 0, 2, IF ( SEARCH ( "Priority: Low", 'Table'[Field Changes], 1, 0 ) > 0, 3 ) ) ) )Note: Pay attention to the standard text format
Step2:
Add two measure by these formulas
Time to High/Critical = VAR _mindatetime0or1 = CALCULATE ( MIN ( 'Table'[AuditDate] ), ALLEXCEPT ( 'Table', 'Table'[IncidentID] ), FILTER ( 'Table', 'Table'[contained] = 0 || 'Table'[contained] = 1 ) ) RETURN DATEDIFF ( CALCULATE ( MIN ( 'Table'[AuditDate] ), ALLEXCEPT ( 'Table', 'Table'[IncidentID] ) ), _mindatetime0or1, MINUTE ) / 60Total Time = DATEDIFF ( CALCULATE ( MIN ( 'Table'[AuditDate] ), ALLEXCEPT ( 'Table', 'Table'[IncidentID] ) ), CALCULATE ( MAX ( 'Table'[AuditDate] ), ALLEXCEPT ( 'Table', 'Table'[IncidentID] ) ), MINUTE ) / 60Step3:
Format the decimal for two measure
Step4:
Drag IncidentID and two measure into visual
here is pbix, please try it.
Best Regards,
Lin
1 Reply
v-lili6-msftCommunity Support
hi, Anonymous
After my research, you could do these as below:
Step1:
Use SEARCH or FIND Function to add a column that based on text contained.
contained = IF ( SEARCH ( "Priority: Critical", 'Table'[Field Changes], 1, 0 ) > 0, 0, IF ( SEARCH ( "Priority: High", 'Table'[Field Changes], 1, 0 ) > 0, 1, IF ( SEARCH ( "Priority: Medium", 'Table'[Field Changes], 1, 0 ) > 0, 2, IF ( SEARCH ( "Priority: Low", 'Table'[Field Changes], 1, 0 ) > 0, 3 ) ) ) )Note: Pay attention to the standard text format
Step2:
Add two measure by these formulas
Time to High/Critical = VAR _mindatetime0or1 = CALCULATE ( MIN ( 'Table'[AuditDate] ), ALLEXCEPT ( 'Table', 'Table'[IncidentID] ), FILTER ( 'Table', 'Table'[contained] = 0 || 'Table'[contained] = 1 ) ) RETURN DATEDIFF ( CALCULATE ( MIN ( 'Table'[AuditDate] ), ALLEXCEPT ( 'Table', 'Table'[IncidentID] ) ), _mindatetime0or1, MINUTE ) / 60Total Time = DATEDIFF ( CALCULATE ( MIN ( 'Table'[AuditDate] ), ALLEXCEPT ( 'Table', 'Table'[IncidentID] ) ), CALCULATE ( MAX ( 'Table'[AuditDate] ), ALLEXCEPT ( 'Table', 'Table'[IncidentID] ) ), MINUTE ) / 60Step3:
Format the decimal for two measure
Step4:
Drag IncidentID and two measure into visual
here is pbix, please try it.
Best Regards,
Lin