Allocate a unique reference number / Generate Start Time or End Time for a set of values

[omillan]

Hello all,

I have been trying to find for the past 3-4 days a formula that can generate a Start Time & End TIme for a dataset that will be automatically adding positions every day. So far I have only been able to find the Earlier function but its keeps bringing me the absolute earliert value with the same "Queue" name. I am looking to consolidate each run ("ID") into a unique job number. In other words, the below example the first 3 lines, it would be job #1, then ID=4, would be job#2 and so on. 

Any ideas on how to solve this? I have tried countrows(), earlier(), min() but I guess not the right way. The closest has been:

Start Time =

MINX(

    FILTER(

        'tfriesen_test_webhook',

        'tfriesen_test_webhook'[Queue] = EARLIER('tfriesen_test_webhook'[Queue])

    ),

    'tfriesen_test_webhook'[Modified]

)

ID	Queue	Modified	Start Time	End Time
1	CreditLimitWF	13/07/2023 01:04:56	13/07/2023 01:04:56	13/07/2023 01:48:20
2	CreditLimitWF	13/07/2023 01:13:29	13/07/2023 01:04:56	13/07/2023 01:48:20
3	CreditLimitWF	13/07/2023 01:48:20	13/07/2023 01:04:56	13/07/2023 01:48:20
4	LGP_Database	13/07/2023 02:06:06	13/07/2023 02:06:06	13/07/2023 02:06:06
5	CreditLimitWF	13/07/2023 02:11:06	13/07/2023 02:11:06	13/07/2023 02:11:54
6	LGP_Database	13/07/2023 02:11:54	13/07/2023 02:11:54	13/07/2023 02:11:54
7	Creditreform_URLs	13/07/2023 04:00:21	13/07/2023 04:00:21	13/07/2023 04:00:26
8	Creditreform_URLs	13/07/2023 04:00:23	13/07/2023 04:00:21	13/07/2023 04:00:26
9	Creditreform_URLs	13/07/2023 04:00:26	13/07/2023 04:00:21	13/07/2023 04:00:26
10	CreditReform_CustomerData	13/07/2023 04:01:36	13/07/2023 04:01:36	13/07/2023 04:02:29
11	CreditReform_CustomerData	13/07/2023 04:02:03	13/07/2023 04:01:36	13/07/2023 04:02:29
12	CreditReform_CustomerData	13/07/2023 04:02:29	13/07/2023 04:01:36	13/07/2023 04:02:29
13	Logistics_Gefahrengutpruefung	13/07/2023 20:18:12	13/07/2023 20:18:12	13/07/2023 20:19:09
14	Logistics_Gefahrengutpruefung	13/07/2023 20:18:43	13/07/2023 20:18:12	13/07/2023 20:19:09
15	Logistics_Gefahrengutpruefung	13/07/2023 20:19:09	13/07/2023 20:18:12	13/07/2023 20:19:09
16	CreditLimitWF	13/07/2023 20:16:53	13/07/2023 20:16:53	13/07/2023 20:16:53
17	Logistics_Gefahrengutpruefung	13/07/2023 20:19:36	13/07/2023 20:19:36	13/07/2023 20:21:01
18	Logistics_Gefahrengutpruefung	13/07/2023 20:20:04	13/07/2023 20:19:36	13/07/2023 20:21:01
19	Logistics_Gefahrengutpruefung	13/07/2023 20:21:01	13/07/2023 20:19:36	13/07/2023 20:21:01

[ryan_mayu · Accepted solution]

@omillan 

pls see the attachment below

[ryan_mayu]

not clear about the expected output. Could you pls also provide the expected output in table?

[ryan_mayu · Accepted solution]

@omillan 

pls see the attachment below

[omillan · Accepted solution]

Thank you Ryan, this is amazing - you did that in 2 minutes and I have been working on it for 3 days.

Here below the solution with 4 columns:

job 1 = if( maxx(FILTER('Table','Table'[Id]=EARLIER('Table'[Id])-1),'Table'[Queue])='Table'[Queue],0,1)

job2 = sumx(FILTER('Table','Table'[Id]<=EARLIER('Table'[Id])),'Table'[job 1])

start = CALCULATE(min('Table'[Modified Date]),ALLEXCEPT('Table','Table'[job2]))

end = CALCULATE(max('Table'[Modified Date]),ALLEXCEPT('Table','Table'[job2]))

If possible, could you explain to me what is the purpose of "maxx" in job 1? Just to be allowed to find out "Earlier" later?

[ryan_mayu]

you can use either maxx or minx. The purpose is to get the previous queue name. If the current queue name is the same as previous one, then it's not a new job. You may have the same queue name in different jobs, so distinct count queue name does not work in your scenario. So that's why i need job 1 first. That's an assistant column and in order to get job 2

[omillan]

Yes of course, I hope the below table is clearer.

Basically anytime, "Queue" name changes that should mark a starting point for that "job". Likewise, it should take the last row before the "Queue" name changes as the end Point.  Expected columns are the last 3 columns:

A) Job # - it should aggregate all queues (transactions) into groups = means, give them a unique reference number.

B) Take the earliest date inside "Modified Date" for that specific Job and allocate it to all the rows in same Job #

C) Same logic but now with the oldest date.

PS: if tthose rows that have one single case and then change, are causing an issue. I already have a formula for that but it would be nice to have one single formula for all.

Id	Queue	Modified Date	A - Job #	B - Start Date	C - Start Date
1	temp	12/07/2023 05:36	1	12/07/2023 05:36	12/07/2023 05:36
2	Freightrates	13/07/2023 00:06	2	13/07/2023 00:06	13/07/2023 00:06
3	UserDisable_Deactivation	13/07/2023 12:01	3	13/07/2023 12:01	13/07/2023 12:01
4	ELDA_DataDownloads	17/07/2023 19:01	4	17/07/2023 19:01	17/07/2023 19:01
5	DangerousGoods_StandardArticles	17/07/2023 20:10	5	17/07/2023 20:10	17/07/2023 20:13
6	DangerousGoods_StandardArticles	17/07/2023 20:11	5	17/07/2023 20:10	17/07/2023 20:13
7	DangerousGoods_StandardArticles	17/07/2023 20:11	5	17/07/2023 20:10	17/07/2023 20:13
8	DangerousGoods_StandardArticles	17/07/2023 20:11	5	17/07/2023 20:10	17/07/2023 20:13
9	DangerousGoods_StandardArticles	17/07/2023 20:12	5	17/07/2023 20:10	17/07/2023 20:13
10	DangerousGoods_StandardArticles	17/07/2023 20:12	5	17/07/2023 20:10	17/07/2023 20:13
11	DangerousGoods_StandardArticles	17/07/2023 20:12	5	17/07/2023 20:10	17/07/2023 20:13
12	DangerousGoods_StandardArticles	17/07/2023 20:13	5	17/07/2023 20:10	17/07/2023 20:13
13	DangerousGoods_StandardArticles	17/07/2023 20:13	5	17/07/2023 20:10	17/07/2023 20:13
14	UserDisable_Deactivation	13/07/2023 12:02	6	13/07/2023 12:02	13/07/2023 12:02
15	Gradebewertung	17/07/2023 20:36	7	17/07/2023 20:36	17/07/2023 20:43
16	Gradebewertung	17/07/2023 20:36	7	17/07/2023 20:36	17/07/2023 20:43
17	Gradebewertung	17/07/2023 20:37	7	17/07/2023 20:36	17/07/2023 20:43
18	Gradebewertung	17/07/2023 20:38	7	17/07/2023 20:36	17/07/2023 20:43
19	Gradebewertung	17/07/2023 20:39	7	17/07/2023 20:36	17/07/2023 20:43
20	Gradebewertung	17/07/2023 20:40	7	17/07/2023 20:36	17/07/2023 20:43
21	Gradebewertung	17/07/2023 20:40	7	17/07/2023 20:36	17/07/2023 20:43
22	Gradebewertung	17/07/2023 20:42	7	17/07/2023 20:36	17/07/2023 20:43
23	Gradebewertung	17/07/2023 20:43	7	17/07/2023 20:36	17/07/2023 20:43
24	Creditreform_URLs	17/07/2023 21:31	8	17/07/2023 21:31	17/07/2023 21:31