Latest Exit

[jbakerstull]

I'm a very new to power BI.  My objective is create a DAX that returns numberic values of 0, 1 in column "Exit".

Exit is defined by using the client latest entry and if an exit occurs prior to "Period_End_Date" then it's considered an exit (value of 1) if not then return an value of 0. 

I learned how to create an DAX formula that identifies client's latest (max) exit date but I'm stuggling how to complete it.  

LatestExitDate = CALCULATE (

   DateValue(

   CALCULATE(

    MAX ( Client[Exit_Date] ),

    ALLEXCEPT ( Client, Client[Clients Personal ID] )

    )))

Clients Personal ID	Start_Date	Exit_Date	Enrollments Reporting Period Start Date	Period_End_Date	Exit
6446	Wednesday, May 24, 2023		Sunday, January 1, 2023	Thursday, June 29, 2023	0
6446	Friday, May 5, 2023	Tuesday, May 9, 2023	Sunday, January 1, 2023	Thursday, June 29, 2023	0
6448	Tuesday, January 17, 2023	Monday, January 23, 2023	Sunday, January 1, 2023	Thursday, June 29, 2023	1
6453	Friday, May 26, 2023	Wednesday, June 28, 2023	Sunday, January 1, 2023	Thursday, June 29, 2023	1
6453	Thursday, May 4, 2023	Wednesday, May 24, 2023	Sunday, January 1, 2023	Thursday, June 29, 2023	0

[v-jianboli-msft · Accepted solution]

Hi @jbakerstull ,

Please try:

Exit = IF(SELECTEDVALUE(Client[Exit_Date])=[LatestExitDate]&&[LatestExitDate]<SELECTEDVALUE(Client[Period_End_Date]),1,0)

Output:

If you need to consider about the blank Exit_Date, please try:

Exit' = 
var _a = CALCULATE(MAX('Client'[Start_Date]),ALLEXCEPT(Client,Client[Clients Personal ID]))
var _b = CALCULATE(MAX('Client'[Exit_Date]),FILTER(ALLEXCEPT(Client,Client[Clients Personal ID]),[Start_Date]=_a))
return IF(_b<>BLANK()&&SELECTEDVALUE(Client[Exit_Date])=_b&&_b<SELECTEDVALUE(Client[Period_End_Date]),1,0)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.