X Lookup within same table

[khisla]

Hello Community

I have the below data columns withiin my table and need to get the below result (i.e. original receipt date column).

I need to x-lookup the retrieved from receipt no. within the receipt no column in order to get the date.  

Want to insert a new column with the date of the receipt no. which is located as another column within the same table.

Receipt No.	Date	Gross Amount (excl. VAT)	Net Amount (excl. VAT)	Sale Is Return Sale	Retrieved from Receipt No.	Orignal Receipt Date
000IL13501000178268	1/9/2025	-	-	TRUE	000IL13501000160901	1/21/2025
000IL13501000175898	1/14/2025	427	299	TRUE	000IL13501000160901	1/21/2025
000IL13501000177412	1/2/2025	-	-	TRUE	000IL13501000177022	1/2/2025
000IL13501000177328	1/2/2025	641	449	TRUE
000IL13501000177022	1/2/2025	-	-	TRUE	000IL13501000178268
000IL13501000181251	1/6/2025	1,197	718	TRUE	000IL13501000175898	1/14/2025
000IL13501000182800	1/21/2025	427	496	TRUE	000IL13501000177412	1/2/2025
000IL13501000160901	1/21/2025	154	108	TRUE	000IL13501000177328	1/2/2025
000IL13501000177331	1/26/2025	-	-	TRUE	000IL13501000177331	1/26/2025

Thanks in advance!!

[quantumudit]

Hello @khisla 

I hope I have correctly understood your query and appreciate the provided sample dataset.

Below is the DAX formula to create a calculated column that performs a lookup within the same table to retrieve the corresponding date based on the given receipt number.

Orignal Receipt Date - Calculated = LOOKUPVALUE('Table'[Date],'Table'[Receipt No.],'Table'[Retrieved from Receipt No.])

Here is the screenshot where you can review the "Expected Original Receipt Date" column and the one made with the DAX calculation, so that you can easily compare the results with the expected ones.

I am also attaching the Power BI file for your reference. Please let me know if you need any further information.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a SuperUser

[quantumudit]

Hello @khisla

I would appreciate your feedback on whether the DAX formula I provided as part of the solution to your query is functioning correctly. Please inform me if it is not working.

Thanks, 

Udit

[Fowmy · Accepted solution]

@khisla 
Add the following Calculated Column to your table, rename the table as per your model":

Orignal Receipt Date = 

VAR __Rno = Table02[Retrieved from Receipt No.]
VAR __Date = MAXX( FILTER( Table02 , Table02[Receipt No.] = __Rno ) , Table02[Date] )
RETURN
    __Date