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

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hey SClarke501,

      That is a bummer.. 

      You would assume that datetime is unique enough. Of course, it is not possible to leave or enter two countries at the same time at exactly the same time.

      If I was responsible for the report, I would report your observation back to the owner of the dataset. This must be an incorrect entry.

      I can not come up with something to make it more unique.. 

      • SClarke501's avatar
        SClarke501
        Frequent Visitor
        4 years ago

        The actual data is not about moving between countries, its about tasks being moved on a kanban board.

         

        I suppose I should have come up with a better set of false data to represent my problem, apologies.

         

        I think I may just have to do some cleansing and handle duplicate columns. I appreciate your help either way