Forum Discussion

SClarke501's avatar
SClarke501
Frequent Visitor
4 years ago
Solved

Getting a previous value using 2 matching columns

I have a set of data similar to this with an two dates, a start and finish, and a location that corresponds to the entry date.

 

IDEntry DateExit DateEnteredLeft
112/12/201724/12/2017USA 
124/12/201703/02/2018Canada 
103/02/2018 Mexico 
204/03/202006/04/2020UK 
206/04/2020 Ireland 
312/09/202013/09/2020Australia 
313/09/202024/11/2020China 
324/11/202010/01/2021Taiwan 
310/01/2021 Japan 

 

I'm trying to use Power Query to create a new column "Left" that shows where that ID entered from. The idea is to check the "Entry Date" for each row and match it against the "Exit Date" column where the ID also matches, and return the location that ID was previously in.

 

For example:

 

IDEntry DateExit DateEnteredLeft
112/12/201724/12/2017USA 
124/12/201703/02/2018CanadaUSA
103/02/2018 MexicoCanada
204/03/202006/04/2020UK 
206/04/2020 IrelandUK
312/09/202013/09/2020Australia 
313/09/202024/11/2020ChinaAustralia
324/11/202010/01/2021TaiwanChina
310/01/2021 JapanTaiwan

 

I can already achieve this using an excel formula: =XLOOKUP(A2&B2,A:A&C:C, D:D, "")

 

However my real dataset is massive and applying the formula to every row crashes Excel, so I've turned to DAX/Power Query to try and recreate it using LOOKUPVALUE, but I'm having trouble figuring it out.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi SClarke501 ,

    You can create a calculated column as below, please find the details in the attachment.

    Left = 
VAR _predate = 
    CALCULATE (
        MAX ( 'Table'[Entry Date] ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Entry Date] < EARLIER ( 'Table'[Entry Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Entered] ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Entry Date] = _predate
        )
    )

    Best Regards

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hey SClarke501 ,

    I have created two extra columns to use the LOOKUPVALUE function:

    Enrty-date = 'Table'[Entry] & "-" & 'Table'[ID]
    Exit-Date = 'Table'[Exit] & "-" & 'Table'[ID]

    Based on these keys, we can use the LOOKUPVALUE function:
    Left = LOOKUPVALUE('Table'[Entered],'Table'[Exit-Date],'Table'[Enrty-date])

     

    I hope this helps you out!

     



    • SClarke501's avatar
      SClarke501
      Frequent Visitor
      4 years ago

      Hi Anonymous 

       

      Great idea on the extra columns, hadn't thought of that. 

       

      However I'm getting an error on the LOOKUPVALUE: A table of multiple values was supplied where a single value was expected.

       

      Is this because the "Entered" column has duplicates in the real data?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi SClarke501,

        No, the entered column should not be the problem. The lookup values has to be unique. That is why I created these keys. I assumed that these are unique. So if an ID enters or exits an country twice on the same day, the key is not unique any more. Can you search the Enrty-date and Exit-date columns we created for duplicates?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi SClarke501 ,

    You can create a calculated column as below, please find the details in the attachment.

    Left = 
VAR _predate = 
    CALCULATE (
        MAX ( 'Table'[Entry Date] ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Entry Date] < EARLIER ( 'Table'[Entry Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Entered] ),
        FILTER (
            'Table',
            'Table'[ID] = EARLIER ( 'Table'[ID] )
                && 'Table'[Entry Date] = _predate
        )
    )

    Best Regards