cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Employee

Hi @carlosarmpb,

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 2
Employee

Hi @carlosarmpb,

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

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors