Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
carlosarmpb
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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)

1.GIF

Best Regards,
Angelia

View solution in original post

2 REPLIES 2
v-huizhn-msft
Microsoft Employee
Microsoft Employee

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)

1.GIF

Best Regards,
Angelia

Seward12533
Solution Sage
Solution Sage

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors