Forum Discussion

carlosarmpb's avatar
carlosarmpb
Frequent Visitor
8 years ago
Solved

Larger registration prior to current

  I would like to know if there is any way to check the longest date before the record I have.   NOTE: I WANT IN THE CONSULTATION EDITOR   The following is an example:   What I have CODE     ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi carlosarmpb,

    Please follow the steps and get right result.

    1. Use your sample table named 'Date_Table'. Create a calculated column using the formula.

    RANK = RANKX(FILTER(Date_Table,Date_Table[CODE]=EARLIER(Date_Table[CODE])),Date_Table[DATE SALE],,ASC,Dense)


    2. Create another calculated column based on 'RANK' column.

    LASTEST SALE = LOOKUPVALUE(Date_Table[DATE SALE],Date_Table[CODE],Date_Table[CODE],Date_Table[RANK],Date_Table[RANK]-1)



    Best Regards,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi carlosarmpb,

Please follow the steps and get right result.

1. Use your sample table named 'Date_Table'. Create a calculated column using the formula.

RANK = RANKX(FILTER(Date_Table,Date_Table[CODE]=EARLIER(Date_Table[CODE])),Date_Table[DATE SALE],,ASC,Dense)


2. Create another calculated column based on 'RANK' column.

LASTEST SALE = LOOKUPVALUE(Date_Table[DATE SALE],Date_Table[CODE],Date_Table[CODE],Date_Table[RANK],Date_Table[RANK]-1)



Best Regards,
Angelia