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,

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