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 |
Solved! Go to Solution.
Thank you @v-zhangti for your help!
I eneded up with 2 calculations. First, I caclulated the last "changed on" date.
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.
Thank you @v-zhangti for your help!
I eneded up with 2 calculations. First, I caclulated the last "changed on" date.
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
User | Count |
---|---|
121 | |
59 | |
56 | |
50 | |
40 |
User | Count |
---|---|
121 | |
60 | |
60 | |
54 | |
49 |