Determine latest change date from a set of identical entries and return another value

[Antje]

For a set of identical "S/N" and "Material desciption", I would need to detremine the "EXW date" bases on the last "Changed on" date". That date needs to be returned to "Final Date". How can I solve this?

S/N	Material description	Changed on	EXW date	Final Date
61	Machine A	6/2/2022	9/13/2022	8/30/2022
61	Machine A	7/1/2022	9/13/2022	8/30/2022
61	Machine A	8/1/2022	8/30/2022	8/30/2022
61	Machine B	9/1/2022	8/12/2022	8/12/2022
62	Machine A	8/2/2022	9/13/2022	9/13/2022
62	Machine A	7/1/2022	9/13/2022	9/13/2022
62	Machine A	6/1/2022	10/1/2022	9/13/2022
62	Machine C	9/1/2022	8/12/2022	8/12/2022

[v-zhangti]

Hi, @Antje 

You can try the following methods.

Column:

Final Date = 
CALCULATE (
    MIN ( 'Table'[EXW date] ),
    FILTER (
        'Table',
        [Material description] = EARLIER ( 'Table'[Material description] )
            && [S/N] = EARLIER ( 'Table'[S/N] )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Antje · Accepted solution]

Thank you @v-zhangti for your help!
I eneded up with 2 calculations. First, I caclulated the last "changed on" date.

Calc Changed on Date=Calculate( MAX('Table'[Changed on]), Filter('Table', 'Table'[Material description] = EARLIER('Table'[Material description]) && 'Table'[S/N] = EARLIER('Table'[S/N])))
Next, I caclulated the corrosponding "EWX date":

Final Date= Calculate( MAX('Table'[EXW Date]), Filter('Table', 'Table'[Material description] = EARLIER('Table'[Material description]) && 'Table'[S/N] = EARLIER('Table'[S/N] && 'Table'[Changed on].[Date] = 'Table'[Calc Changed on Date]))

[CSN]

Hi Antje,

Within Power Query you can use an advanced group by to group this table on S/N and Material description. Create a new column (Final Date) based on the EXW date with Max as operation.

Then you can merge your existing query (on S/N and Material description) with this query and add the new column to it.

Best regards,

Coen