Calculate Column for Learning Program Completion Date based on list of Course Completions

[Casey_Alderson]

Hi All, 

I'm trying to figure out how I can calculate a Learning Program completion date when my primary table is a list of courses taken by multiple employees. Below are sample tables. In the scenario, I'm calculating for Program Completion Date and I need to return the MAX Completion Date from table 1 by Employee ID and it can only populate if there is a completion date for all Enrolled Content (A, B, C, & D). The expected results would be Employee 1 = 1/15/23; Employee 2 = 2/11/23; and Employee 3 = "null" because they haven't completed C or D.

Thanks!

Casey

[Casey_Alderson · Accepted solution]

Hi All, 

I was able to find a solution via hours of google and rinse and repeat tests. 

= IF(

NOT(ISBLANK(LOOKUPVALUE([Completion Date], Table1[EmployeeID], Table2[EmpoyeeID],Table1[EnrolledContent], "A"))) ** NOT...repeat the above for all required courses),
MAXX(
{
MAX(LOOKUPVALUE([Completion Date], Table1[EmployeeID], Table2[EmpoyeeID],Table1[EnrolledContent], "A"), LOOKUPVALUE([Completion Date], Table1[EmployeeID], Table2[EmpoyeeID],Table1[EnrolledContent], "B")),
MAX(LOOKUPVALUE([Completion Date], Table1[EmployeeID], Table2[EmpoyeeID],Table1[EnrolledContent], "C"), LOOKUPVALUE([Completion Date], Table1[EmployeeID], Table2[EmpoyeeID],Table1[EnrolledContent], "D"))
}, 
[Value]
)
)

Crazy to me but it works!

[amitchandak]

@Casey_Alderson , if you need a new column

Maxx(filter(Table, Table[Employee ID] = earlier( Table[Employee ID]) ), [Completion Date] )

A measure =

Maxx(filter(all(Table), Table[Employee ID] = Max( Table[Employee ID]) ), [Completion Date] )

You can also check this way

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[Casey_Alderson]

Hi, 

I don't see how this method addresses the need to ensure that the 4 required courses are completed. Won't this just give me the latest completion date by Employee ID?

Thanks!