HELP! IF statement with GROUP BY logic

[jts_]

Hi,

As the attached picture shows, I have the data of a customer's historical visit date & the future appointment date.

As long as this customer has a future appointment booked (>= today), he/ she will be identified as an active customer, otherwise, this person will be marked as an inactive customer. Also, I would like to track the most recent appointment date of these active customers.

I have been trying to utilize min() & max() function to identify active/ inactive users. However, that is not practical since the appointment that occurred in the past will be identified as inactive, and it will create an ambiguity that a person will be categorized as both active and inactive at the same time.

I highly appreciate your thoughts/ comments in advance!

[mohammedadnant · Accepted solution]

Hi @jts_ 

Interesting requirement,

please create 2 measures

ActiveOrNot = 
VAR _MaxDate =
CALCULATE ( MAX ( VisitorTable[VisitDate] ), ALL ( VisitorTable[VisitDate] ) )
RETURN
IF ( _MaxDate < TODAY (), "No", "Yes" )

NextVisitDate = 
CALCULATE (
MIN ( VisitorTable[VisitDate] ),
ALL ( VisitorTable[VisitDate] ),
VisitorTable[VisitDate] > TODAY ()
)

If this solves your issue, please give thumbs up (kudos) and marked this answer as your solution.

Thanks & Regards,

Mohammed Adnan

Learn Power BI: https://www.youtube.com/c/taik18

[jts_]

Thank you @mohammedadnant !

This is exactly what I was looking for, and it perfectly solve my issues!

Thanks a lot!!!