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 "P...
  • 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.

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.