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.   ID Entry Date Exit Date Entered Left 1 12/12/2017 24/12/...
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

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?

      • SClarke501's avatar
        SClarke501
        Frequent Visitor
        4 years ago

        Yes unfortunately I spotted a duplicate. Very surprising since my real data has datetime instead of just date. But there are two entries that apparently happen on the exact same date at the exact same time!

         

        Is there any way around this? I thought to maybe append some more data to the new columns to make them unique but there isn't anything to use.