cancel
Showing results for 
Search instead for 
Did you mean: 

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

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Power BI Fabric Summit Carousel

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