DAX Formula to find last day worked within a week

[Anonymous]

I am trying to develop a formula to calculate the last working day within a work week for a group of employees.  A work week runs from Monday to Sunday.  The group as a whole will work 5-7 days per week, where an individual may work any number of days during the week.  The Last Work Date will be the same for all employees who work during a work week regardless of the number of days worked during the week.

My table is represented below.   I need to calculate the last date worked for the group of each week.  I have filled in the Last Work Date column with the desired results.  Please note, in the example below RC did not record production on 6/27/20, but the Last Day Worked is 6/27/20 based on production dates of the other employees.

Employee	Week Beginning Date	Production	Production Date	Last Work Date of Week
RC	6/8/2020	5	6/13/2020	6/13/2020
RC	6/15/2020	4	6/16/2020	6/19/2020
TB	6/15/2020	1	6/16/2020	6/19/2020
RC	6/15/2020	3	6/17/2020	6/19/2020
TB	6/15/2020	2	6/17/2020	6/19/2020
FB	6/15/2020	3	6/18/2020	6/19/2020
RC	6/15/2020	4	6/18/2020	6/19/2020
TB	6/15/2020	2	6/18/2020	6/19/2020
FB	6/15/2020	5	6/19/2020	6/19/2020
RC	6/15/2020	1	6/19/2020	6/19/2020
TB	6/15/2020	1	6/19/2020	6/19/2020
FB	6/22/2020	3	6/22/2020	6/27/2020
RC	6/22/2020	5	6/22/2020	6/27/2020
TB	6/22/2020	1	6/22/2020	6/27/2020
FB	6/22/2020	2	6/23/2020	6/27/2020
RC	6/22/2020	2	6/23/2020	6/27/2020
TB	6/22/2020	4	6/23/2020	6/27/2020
FB	6/22/2020	3	6/24/2020	6/27/2020
RC	6/22/2020	1	6/24/2020	6/27/2020
TB	6/22/2020	5	6/24/2020	6/27/2020
RC	6/22/2020	3	6/25/2020	6/27/2020
TB	6/22/2020	11	6/25/2020	6/27/2020
FB	6/22/2020	8	6/26/2020	6/27/2020
RC	6/22/2020	4	6/26/2020	6/27/2020
TB	6/22/2020	2	6/26/2020	6/27/2020
FB	6/22/2020	2	6/27/2020	6/27/2020
TB	6/22/2020	4	6/27/2020	6/27/2020

Any assistance would be appreciated.

[lbendlin]

Here's a solution that doesn't require any computation at all

[Anonymous]

@lbendlin   Thanks, but I need a column in the table because I will need it for another calculation.  Ultimately, I will summarize the production by the Week Beginning Date and compute the average for the week based on the datediff(week beginning date,last day worked).

[lbendlin]

Last Day Worked := CALCULATE(max(Worked[Production Date]),ALLEXCEPT(Worked,Worked[Last Work Date of Week]))

[Anonymous · Accepted solution]

@lbendlin   You were close.  The Last Work Date of Week is the value I'm trying to calculate.  The correct formula would be:

CALCULATE(max(Worked[Production Date]),ALLEXCEPT(Worked,Worked[Week Beginning Date]))

Thanks,  I would have sworn I tried this formula before I posted the question.