cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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/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

1 ACCEPTED SOLUTION
Frequent Visitor

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]))
3 REPLIES 3
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] )
)
)``````

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.

Frequent Visitor

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors