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]) IDQueueModifiedStart TimeEnd Time1CreditLimitWF13/07/2023 01:04:5613/07/2023 01:04:5613/07/2023 01:48:202CreditLimitWF13/07/2023 01:13:2913/07/2023 01:04:5613/07/2023 01:48:203CreditLimitWF13/07/2023 01:48:2013/07/2023 01:04:5613/07/2023 01:48:204LGP_Database13/07/2023 02:06:0613/07/2023 02:06:0613/07/2023 02:06:065CreditLimitWF13/07/2023 02:11:0613/07/2023 02:11:0613/07/2023 02:11:546LGP_Database13/07/2023 02:11:5413/07/2023 02:11:5413/07/2023 02:11:547Creditreform_URLs13/07/2023 04:00:2113/07/2023 04:00:2113/07/2023 04:00:268Creditreform_URLs13/07/2023 04:00:2313/07/2023 04:00:2113/07/2023 04:00:269Creditreform_URLs13/07/2023 04:00:2613/07/2023 04:00:2113/07/2023 04:00:2610CreditReform_CustomerData13/07/2023 04:01:3613/07/2023 04:01:3613/07/2023 04:02:2911CreditReform_CustomerData13/07/2023 04:02:0313/07/2023 04:01:3613/07/2023 04:02:2912CreditReform_CustomerData13/07/2023 04:02:2913/07/2023 04:01:3613/07/2023 04:02:2913Logistics_Gefahrengutpruefung13/07/2023 20:18:1213/07/2023 20:18:1213/07/2023 20:19:0914Logistics_Gefahrengutpruefung13/07/2023 20:18:4313/07/2023 20:18:1213/07/2023 20:19:0915Logistics_Gefahrengutpruefung13/07/2023 20:19:0913/07/2023 20:18:1213/07/2023 20:19:0916CreditLimitWF13/07/2023 20:16:5313/07/2023 20:16:5313/07/2023 20:16:5317Logistics_Gefahrengutpruefung13/07/2023 20:19:3613/07/2023 20:19:3613/07/2023 20:21:0118Logistics_Gefahrengutpruefung13/07/2023 20:20:0413/07/2023 20:19:3613/07/2023 20:21:0119Logistics_Gefahrengutpruefung13/07/2023 20:21:0113/07/2023 20:19:3613/07/2023 20:21:01

omillan pls see the attachment below

Forum Discussion

omillan

New Member

3 years ago

Solved

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

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

Need Help

ryan_mayu
3 years ago
omillan

pls see the attachment below
Allocate a unique reference number.pbix25 KB
omillan
3 years ago
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?

5 Replies

ryan_mayu

Super User

3 years ago

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

ryan_mayu
Super User
3 years ago
omillan

pls see the attachment below
Allocate a unique reference number.pbix25 KB
- omillan
  New Member
  3 years ago
  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?

omillan

New Member

3 years ago

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

Forum Discussion

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

5 Replies

Related content

Cost allocation

Generating Missing Data

Monthly sales budget data allocation to daily grain

power bi license allocation

dynamic reference line