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

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