Max Date Prior to a Filtered Range?

[jcawley]

Hello,

I'm trying to see how many customers have came in during the last month (or any filtered range really) that have a completed appointment, who have purchased something previously from us, and who have not been into our office in the last 365 days (relative to any filtered date range).

I have two tables. One table (1) shows all customers who have purchased an item, and another table (2) which shows all the dates in which a customer has come to the store.

Table 1

ID

12345

23456

Table 2

ID	Type	Date	Completed
12345	CheckUp	06/01/2014	Yes
12345	CheckUp	06/28/2020	Yes
23456	CheckUp	06/01/2014	Yes
23457	CheckUp	06/01/2020	No

So far I tried to use the following as suggested by @AllisonKennedy:

Previous Visit Date = MAXX(FILTER(Table2, Table2[Date]<EARLIER(Table2[Date])),Table2[Date])

 - I can't seem to get this to spit out the correct info. It gives dates, but it doesn't seem to be specific to ID

Purchased = IF(COUNTROWS(RELATEDTABLE(Table1))>0,"Yes","No")

Then create a MEASURE to determine if they are your key client or not: 

KeyClient = IF(DATEDIFF(SELECTEDVALUE(Table2[Previous Visit Date]),MIN(DimDate[Date]),DAY)>365&&SELECTEDVALUE(Table2[Completed])="Yes" && SELECTEDVALUE(Table2[Purchased])="Yes","Yes", "No")

  - Can't get this measure to give me a count!

Any help would be greatly apprecaited!

[AllisonKennedy · Accepted solution]

Also, sorry I thought I had updated it, but try this updated version of Previous Visit Date that checks for ID:
Previous Visit Date = MAXX(FILTER(Table2, Table2[ID]=EARLIER(Table2[ID]) &&Table2[Date]<EARLIER(Table2[Date])),Table2[Date])

[AllisonKennedy]

Can you provide a sample of your desired output? The measure I suggested works if you have ID in the visual, but if you're not going to have ID in that visual you could try a variation of what I suggested in your other post, only using a COUNTROWS(FILTER(Table1, ...

You'll need to slightly ammend the filter functions accordingly to get them to talk to the Table1 data based on your relationships. You also won't need the filter for Purchased="Yes" since Table1 already does that for you.