Forum Discussion

khisla's avatar
khisla
Helper II
1 year ago
Solved

X Lookup within same table

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 SaleRetrieved from Receipt No.Orignal Receipt Date
000IL135010001782681/9/2025                            -                        -  TRUE000IL135010001609011/21/2025
000IL135010001758981/14/2025                       427                 299TRUE000IL135010001609011/21/2025
000IL135010001774121/2/2025                            -                        -  TRUE000IL135010001770221/2/2025
000IL135010001773281/2/2025                       641                 449TRUE  
000IL135010001770221/2/2025                            -                        -  TRUE000IL13501000178268 
000IL135010001812511/6/2025                  1,197                 718TRUE000IL135010001758981/14/2025
000IL135010001828001/21/2025                       427                 496TRUE000IL135010001774121/2/2025
000IL135010001609011/21/2025                       154                 108TRUE000IL135010001773281/2/2025
000IL135010001773311/26/2025                            -                        -  TRUE000IL135010001773311/26/2025

 

Thanks in advance!!

  • Fowmy's avatar
    Fowmy
    1 year ago

    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

     

     

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    1 year ago

    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

     

     

     

  • quantumudit's avatar
    quantumudit
    Super User
    1 year ago

    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's avatar
      quantumudit
      Super User
      1 year ago

      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