Forum Discussion
Allocate a unique reference number / Generate Start Time or End Time for a set of values
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?
Super Usernot clear about the expected output. Could you pls also provide the expected output in table?
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 |