Forum Discussion
Calculate Column for Learning Program Completion Date based on list of Course Completions
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
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!
3 Replies
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-d0cf2fcf92d0Hi,
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!
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!
