Return when a value changes

Question

This is my data

SerialNumber  Reading  Date
A             4        10/30/2019
A             4        10/29/2019
A             3        10/28/2019
B             11       10/30/2019
B             9        10/29/2019
B             6        10/28/2019
C             25       10/30/2019
C             25       10/15/2019
C             25       10/13/2019
C             21       10/9/2019
C             21       10/3/2019

I need to be able to either create a measure or a calculated column that tells me the date of when each serial number last changed. So i need this:

SerialNumber  Reading  Date          DateChanged
A             4        10/30/2019    10/28/2019
A             4        10/29/2019    10/28/2019
A             3        10/28/2019    10/28/2019
B             11       10/30/2019    10/29/2019
B             9        10/29/2019    10/29/2019
B             6        10/28/2019    10/29/2019
C             25       10/30/2019    10/9/2019
C             25       10/15/2019    10/9/2019
C             25       10/13/2019    10/9/2019
C             21       10/9/2019     10/9/2019
C             21       10/3/2019     10/9/2019

Here is the code I tried, but it only gave me the max date for each SerialNumber

Column = CALCULATE(
MAX(
SMU_Lookup[Date]),
FILTER(
ALL(SMU_Lookup),
 SMU_Lookup[Reading] < MAX(SMU_Lookup[Reading])
 && SMU_Lookup[SerialNumber] = EARLIER(SMU_Lookup[SerialNumber])
)
)

Thanks in advance!

Answer 1

I think this is the calculated column you are looking for:

DateChanged =
VAR max_date =
    MAXX (
        ALLEXCEPT ( 'SMU_Lookup', SMU_Lookup[SerialNumber] ),
        'SMU_Lookup'[Date]
    )
VAR reading_max_date =
    CALCULATE (
        MAX ( 'SMU_Lookup'[Reading] ),
        ALLEXCEPT ( 'SMU_Lookup', SMU_Lookup[SerialNumber] ),
        'SMU_Lookup'[Date] = max_date
    )
RETURN
    CALCULATE (
        MAX ( SMU_Lookup[Date] ),
        ALLEXCEPT ( 'SMU_Lookup', SMU_Lookup[SerialNumber] ),
        'SMU_Lookup'[Reading] <> reading_max_date
    )

1. The ALLEXCEPT-function returns a table with the rows where the SerialNumber is the same as the SerialNumber in the current row.
2. The MAXX function returns the latest date for that table.
3. [VAR reading_max_date] holds the reading for the max_date for each SerialNumber.
4. After the RETURN statement the MAX(Date) is calculated where the Reading differs from reading_max_date.

Answer 2

I first created a measure (a variable may have worked here too) to hold the max reading for each SerialNumber

MaxReading = CALCULATE(MAX(SMU_Lookup[Reading]),ALLEXCEPT(SMU_Lookup, SMU_Lookup[SerialNumber]))

Then I created the calculated column LastUpdate as follows

LastUpdate = CALCULATE(MAX(SMU_Lookup[Date]), FILTER(ALLEXCEPT(SMU_Lookup, SMU_Lookup[SerialNumber]), SMU_Lookup[Reading] < [Max Reading]))

Simple enough and it works for the instances where there are multiple readings with the same SerialNumber and Date.