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?
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
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