Forum Discussion
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 ...
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?
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
Super User
Super Useryou 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