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        DATE SALE 
1001          01/01/2017
1001          02/01/2017 
1001          03/01/2017
1003          04/01/2017
1003          05/01/2017
1003          06/01/2017
1004          03/01/2017
1004          04/01/2017
1004          05/01/2017

 

 

What I want

CODE        DATE SALE        LASTEST SALE
1001          01/01/2017        null
1001          02/01/2017        01/01/2017
1001          03/01/2017        02/01/2017
1003          04/01/2017        null
1003          05/01/2017        04/01/2017
1003          06/01/2017        05/01/2017
1004          03/01/2017        null
1004          04/01/2017        03/01/2017
1004          05/01/2017        04/01/2017

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

2 Replies

  • 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

  • Seward12533's avatar
    Seward12533
    Icon for Solution Sage rankSolution Sage
    8 years ago

    What is the Consultation Editor? I think you shoudl do it in DAX as I think the M language in the Query Editor would be very messy. If you need a table to link to your model you can use the NEW TABLE function to use DAX to build table that you can then link into your model or if you just need to display you can do in a visual using measures or add to your fact table with a calculated column.

     

    I do't have time right now to think through the solution but trick is using the Earlier function. There have been at several questions on this with solutions in the last few days and probably many more. so a search throug the forum or online shoudl get you want you neeed. Here is one I found with a quick google search that seems to fit the bill - https://community.powerbi.com/t5/Desktop/add-previous-row-values/td-p/114648