Forum Discussion

jbakerstull's avatar
jbakerstull
Regular Visitor
3 years ago
Solved

Latest Exit

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 IDStart_DateExit_DateEnrollments Reporting Period Start DatePeriod_End_DateExit
6446Wednesday, May 24, 2023 Sunday, January 1, 2023Thursday, June 29, 20230
6446Friday, May 5, 2023Tuesday, May 9, 2023Sunday, January 1, 2023Thursday, June 29, 20230
6448Tuesday, January 17, 2023Monday, January 23, 2023Sunday, January 1, 2023Thursday, June 29, 20231
6453Friday, May 26, 2023Wednesday, June 28, 2023Sunday, January 1, 2023Thursday, June 29, 20231
6453Thursday, May 4, 2023Wednesday, May 24, 2023Sunday, January 1, 2023Thursday, June 29, 20230

 

  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

1 Reply

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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.