Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

earlier function with mutliple criteria

hi,

 

i am stuck with problem having two creteria, using earlier function.

  

Unit | Rate |Date 

Cat |5       | 01-12-2017 1:25

Cat |4       | 02-12-2017 2:25

Rat |9       | 01-12-2017 3:25

Cat |3       | 02-12-2017 4:25

Cat |2       | 01-12-2017 6:25

Rat |8       | 01-12-2017 8:25

 

then i want to get the Rate of earlier date & time, of same unit in a calculated column.

 

please help

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi Anonymous,

    Please create a calculated column using the formula below.

    Column =
LOOKUPVALUE (
    Test[Rate],
    Test[Unit], Test[Unit],
    Test[Date], CALCULATE ( MIN ( Test[Date] ), ALLEXCEPT ( Test, Test[Unit] ) )
)


    You will get the expected result as follows.



    Best Regards,
    Angelia

1 Reply

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

    Please create a calculated column using the formula below.

    Column =
LOOKUPVALUE (
    Test[Rate],
    Test[Unit], Test[Unit],
    Test[Date], CALCULATE ( MIN ( Test[Date] ), ALLEXCEPT ( Test, Test[Unit] ) )
)


    You will get the expected result as follows.



    Best Regards,
    Angelia