Forum Discussion
Get value from other row in same table
Hi everyone. Need some help. My data is formatted like this (extraneous columns removed) (dates dd/mm/yyyy)
| Serial1 | Date1 | Serial2 | Date2 |
| abc | 01/01/2019 | bcd | 01/05/2019 |
| bcd | 06/07/2019 | cde | 08/10/2019 |
| cde | 01/11/2019 | def | 02/03/2020 |
| vwx | 02/01/2019 | wxy | 02/05/2019 |
| wxy | 05/06/2019 | xyz | 13/08/2019 |
| xyz | 12/10/2019 | yza | 02/02/2020 |
So what I'm trying to do is, create a calculated column that for a given row returns the EARLIEST value of Date1 that satisfies the following criteria
Serial1 in the row from which date1 is selected = Serial2 in the original row, selected value of Date1 >= Date2 in the original row.
I've tried this:
Date3 = CALCULATE(
FIRSTDATE(TableName[Date1]),
TableName[Date1] >= EARLIER(TableName[Date2]),
TableName[Serial1] = Earlier(TableName[Serial2])
)
To no avail, I end up getting a circular dependency error, between the calculated column and a hex value that appears to be a memory address.
The result should come out:
| Serial1 | Date1 | Serial2 | Date2 | Date3 |
| abc | 01/01/2019 | bcd | 01/05/2019 | 06/07/2019 |
| bcd | 06/07/2019 | cde | 08/10/2019 | 01/11/2019 |
| cde | 01/11/2019 | def | 02/03/2020 | <blank> |
| vwx | 02/01/2019 | wxy | 02/05/2019 | 05/06/2019 |
| wxy | 05/06/2019 | xyz | 13/08/2019 | 12/10/2019 |
| xyz | 12/10/2019 | yza | 02/02/2020 | <blank> |
Would appreciate some help!
I did the following:
Date3 = MINX( FILTER('Table', 'Table'[Date1] >= EARLIER('Table'[Date2]) && 'Table'[Serial1] = Earlier('Table'[Serial2]) ), [Date1] )PBIX is attached.
1 Reply
I did the following:
Date3 = MINX( FILTER('Table', 'Table'[Date1] >= EARLIER('Table'[Date2]) && 'Table'[Serial1] = Earlier('Table'[Serial2]) ), [Date1] )PBIX is attached.