Forum Discussion

omillan's avatar
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 ...
Need Help
  • omillan's avatar
    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?
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
3 years ago

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

  • omillan's avatar
    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?
    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      3 years ago

      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