Calculate difference between two status changes

[elsteshepardo]

Hi,

I foolishly 'broke' a powerbi report by accidentally deleting a query and additional columns I'd added by using Google & this forum

I now can't find the right answer

I have a sharepoint list which is a transaction log - keeping a track on status changes for new requests.

The table looks a bit like this

Title (Request number)	Status	Modified (date)
001	Received	20/3/23
001	Reviewed	25/3/23
002	Received	30/3/23
003	Received	1/4/23
002	Reviewed	10/4/23
004	Received	15/4/23

As you can see, it's just a series of status changes and the date is was made - it's from a Flow that runs each time an item is updated. Anyway, what I need is a duration (difference) between status updates per Request

So in the above table it would have something like

Title	Status	Duration
001	Received	=25/3/23 - 20/3/23 = 5 days
002	Received	=10/4/23 - 30/3/23 = 11 days

Could someone help - I seem to remember it was a calculated column

Thanks

Steven

[elsteshepardo]

Would anyboby be able to help?

Thanks in advance

[DataVitalizer]

Hi @elsteshepardo 

Can you open the advanced editor of your table? If so paste here to make the edit easy for you. 

Did it work ? 👌 Mark it as a solution to help spreading knowledge 👉 A kudos would be appreciated

[elsteshepardo]

Is this what you mean? I opened advanced editor from the query:

let
Source = SharePoint.Tables("https://nhs.sharepoint.com/sites/*********", [Implementation="2.0", ViewMode="All"]),
#"b0118a61-d46c-4d5f-ade5-8b12eaec27f8" = Source{[Id="b0118a61-d46c-4d5f-ade5-8b12eaec27f8"]}[Items],
#"Removed Other Columns" = Table.SelectColumns(#"b0118a61-d46c-4d5f-ade5-8b12eaec27f8",{"Title", "Status", "Created", "Modified", "ID", "Version"})
in
#"Removed Other Columns"

[elsteshepardo]

TitleStatusCreatedModifiedIDVersionStatus Order

401	For assessment	25/01/2023 12:38:00	25/01/2023 12:38:00	3	1.0	3
301	For assessment	25/01/2023 13:16:00	25/01/2023 13:16:00	4	1.0	3
401	Under investigation	25/01/2023 13:16:00	25/01/2023 13:16:00	5	1.0	2
401	For SLT review	25/01/2023 14:53:00	25/01/2023 14:53:00	6	1.0	4
243	Approved to next stage	26/01/2023 10:35:00	26/01/2023 10:35:00	14	1.0	7
244	Approved to next stage	26/01/2023 10:35:00	26/01/2023 10:35:00	15	1.0	7
248	Approved to next stage	26/01/2023 10:40:00	26/01/2023 10:40:00	16	1.0	7
242	Approved to next stage	26/01/2023 11:11:00	26/01/2023 11:11:00	17	1.0	7
301	Request completed	23/02/2023 14:39:00	23/02/2023 14:39:00	18	1.0	10
301	Request completed	23/02/2023 14:39:00	23/02/2023 14:39:00	19	1.0	10
301	Request received	23/02/2023 14:44:00	23/02/2023 14:44:00	20	1.0	1
301	For SLT review	23/02/2023 14:46:00	23/02/2023 14:46:00	21	1.0	4
301	Approved to next stage	23/02/2023 14:48:00	23/02/2023 14:48:00	22	1.0	7
301	Request now a project	23/02/2023 14:49:00	23/02/2023 14:49:00	23	1.0	9
262	Request received	23/02/2023 15:23:00	23/02/2023 15:23:00	24	1.0	1
262	Under investigation	24/02/2023 08:44:00	24/02/2023 08:44:00	25	1.0	2
259	Under investigation	24/02/2023 08:44:00	24/02/2023 08:44:00	26	1.0	2
261	Under investigation	24/02/2023 08:44:00	24/02/2023 08:44:00	27	1.0	2
262	For assessment	01/03/2023 11:20:00	01/03/2023 11:20:00	28	1.0	3
240	Request completed	02/03/2023 15:20:00	02/03/2023 15:20:00	29	1.0	10
228	Request completed	02/03/2023 15:29:00	02/03/2023 15:29:00	30	1.0	10
232	Approved to next stage	08/03/2023 12:55:00	08/03/2023 12:55:00	32	1.0	7
233	Approved to next stage	08/03/2023 12:57:00	08/03/2023 12:57:00	33	1.0	7
236	Approved to next stage	08/03/2023 12:58:00	08/03/2023 12:58:00	34	1.0	7
252	Approved to next stage	08/03/2023 13:00:00	08/03/2023 13:00:00	35	1.0	7
253	Approved to next stage	08/03/2023 13:01:00	08/03/2023 13:01:00	36	1.0	7
254	Approved to next stage	08/03/2023 13:03:00	08/03/2023 13:03:00	37	1.0	7
255	Approved to next stage	08/03/2023 13:03:00	08/03/2023 13:03:00	38	1.0	7
256	Approved to next stage	08/03/2023 13:04:00	08/03/2023 13:04:00	39	1.0	7
257	Approved to next stage	08/03/2023 13:05:00	08/03/2023 13:05:00	40	1.0	7
258	Approved to next stage	08/03/2023 13:06:00	08/03/2023 13:06:00	41	1.0	7
265	Under investigation	09/03/2023 14:43:00	09/03/2023 14:43:00	42	1.0	2
264	Under investigation	09/03/2023 14:43:00	09/03/2023 14:43:00	43	1.0	2
229	For SLT review	09/03/2023 15:15:00	09/03/2023 15:15:00	44	1.0	4
266	Under investigation	14/03/2023 09:45:00	14/03/2023 09:45:00	45	1.0	2
263	For SLT review	14/03/2023 11:03:00	14/03/2023 11:03:00	46	1.0	4
229	Approved to next stage	14/03/2023 11:18:00	14/03/2023 11:18:00	47	1.0	7
266	For assessment	15/03/2023 10:38:00	15/03/2023 10:38:00	48	1.0	3
176	Request completed	17/03/2023 13:55:00	17/03/2023 13:55:00	49	1.0	10
213	Request completed	17/03/2023 16:25:00	17/03/2023 16:25:00	50	1.0	10
268	For Clinical review	23/03/2023 14:31:00	23/03/2023 14:31:00	51	1.0	5
208	Request completed	23/03/2023 16:13:00	23/03/2023 16:13:00	52	1.0	10
217	Request completed	23/03/2023 16:14:00	23/03/2023 16:14:00	53	1.0	10
243	Request completed	23/03/2023 16:19:00	23/03/2023 16:19:00	54	1.0	10
259	For assessment	24/03/2023 14:42:00	24/03/2023 14:42:00	55	1.0	3
265	For SLT review	28/03/2023 09:28:00	28/03/2023 09:28:00	56	1.0	4
267	Approved to next stage	28/03/2023 12:05:00	28/03/2023 12:05:00	57	1.0	7
263	Approved to next stage	28/03/2023 12:05:00	28/03/2023 12:05:00	58	1.0	7
264	Approved to next stage	28/03/2023 12:06:00	28/03/2023 12:06:00	59	1.0	7
260	Approved to next stage	28/03/2023 12:14:00	28/03/2023 12:14:00	60	1.0	7
271	For assessment	30/03/2023 09:42:00	30/03/2023 09:42:00	61	1.0	3
205	Request completed	31/03/2023 10:11:00	31/03/2023 10:11:00	62	1.0	10
227	Request completed	31/03/2023 14:27:00	31/03/2023 14:27:00	63	1.0	10
272	Under investigation	03/04/2023 10:24:00	03/04/2023 10:24:00	64	1.0	2
270	For assessment	03/04/2023 16:13:00	03/04/2023 16:13:00	65	1.0	3
271	For SLT review	04/04/2023 08:54:00	04/04/2023 08:54:00	66	1.0	4
270	For SLT review	04/04/2023 08:54:00	04/04/2023 08:54:00	67	1.0	4
259	For SLT review	04/04/2023 08:54:00	04/04/2023 08:54:00	68	1.0	4
268	For assessment	04/04/2023 15:35:00	04/04/2023 15:35:00	69	1.0	3
259	Approved to next stage	11/04/2023 10:17:00	11/04/2023 10:17:00	70	1.0	7
270	Rejected	11/04/2023 10:18:00	11/04/2023 10:18:00	71	1.0	6
271	Approved to next stage	11/04/2023 10:18:00	11/04/2023 10:18:00	72	1.0	7
265	Approved to next stage	11/04/2023 10:18:00	11/04/2023 10:18:00	73	1.0	7
165	Request completed	11/04/2023 14:22:00	11/04/2023 14:22:00	74	1.0	10
211	Request completed	11/04/2023 14:33:00	11/04/2023 14:33:00	75	1.0	10
174	Approved to next stage	11/04/2023 15:55:00	11/04/2023 15:55:00	76	1.0	7
275	Under investigation	12/04/2023 09:28:00	12/04/2023 09:28:00	77	1.0	2
246	Rejected	12/04/2023 09:42:00	12/04/2023 09:42:00	78	1.0	6
271	Request now a project	12/04/2023 11:20:00	12/04/2023 11:20:00	79	1.0	9
235	Rejected	12/04/2023 16:06:00	12/04/2023 16:06:00	80	1.0	6
275	For Clinical review	17/04/2023 11:35:00	17/04/2023 11:35:00	81	1.0	5
278	For Clinical review	17/04/2023 11:35:00	17/04/2023 11:35:00	82	1.0	5
273	For Clinical review	17/04/2023 11:35:00	17/04/2023 11:35:00	83	1.0	5
276	Proceeded without SLT approval	17/04/2023 13:36:00	17/04/2023 13:36:00	84	1.0	8
274	For assessment	17/04/2023 13:40:00	17/04/2023 13:40:00	85	1.0	3
274	For SLT review	18/04/2023 10:33:00	18/04/2023 10:33:00	86	1.0	4
277	For SLT review	18/04/2023 10:33:00	18/04/2023 10:33:00	87	1.0	4
209	Request completed	18/04/2023 12:40:00	18/04/2023 12:40:00	88	1.0	10
234	Rejected	18/04/2023 14:05:00	18/04/2023 14:05:00	89	1.0	6
171	Rejected	20/04/2023 09:39:00	20/04/2023 09:39:00	90	1.0	6
183	Rejected	20/04/2023 09:49:00	20/04/2023 09:49:00	91	1.0	6
231	Rejected	20/04/2023 09:51:00	20/04/2023 09:51:00	92	1.0	6
275	For assessment	20/04/2023 16:14:00	20/04/2023 16:14:00	93	1.0	3
278	For assessment	20/04/2023 16:14:00	20/04/2023 16:14:00	94	1.0	3
273	For assessment	20/04/2023 16:14:00	20/04/2023 16:14:00	95	1.0	3
178	For assessment	20/04/2023 16:15:00	20/04/2023 16:15:00	96	1.0	3
232	For SLT review	21/04/2023 12:36:00	21/04/2023 12:36:00	97	1.0	4
274	Approved to next stage	25/04/2023 09:45:00	25/04/2023 09:45:00	98	1.0	7
277	Approved to next stage	25/04/2023 09:45:00	25/04/2023 09:45:00	99	1.0	7

[elsteshepardo]

Hi, not sure what you mean by that?

The table in the dataset? Or the query?