cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Antje
Frequent Visitor

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

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/NMaterial descriptionChanged onEXW dateFinal Date
61Machine A6/2/20229/13/20228/30/2022
61Machine A7/1/20229/13/20228/30/2022
61Machine A8/1/20228/30/20228/30/2022
61Machine B9/1/20228/12/20228/12/2022
62Machine A8/2/20229/13/20229/13/2022
62Machine A7/1/20229/13/20229/13/2022
62Machine A6/1/202210/1/20229/13/2022
62Machine C9/1/20228/12/20228/12/2022

 

 
1 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]))

View solution in original post

3 REPLIES 3
v-zhangti
Community Support
Community Support

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] )
    )
)

 

vzhangti_0-1662537490003.png

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.

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
New Member

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

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors