New Account (1st purchase and if customer has not purchased for last 3years)

[akhatri]

Hi Everyone,

I've been trying to get my head around this but am struggling. I have the formula to flag New Accounts which is working great (below),

Customer first appearance text =

VAR _minDate =

CALCULATE (

    MIN ('SFDC Opp'[Bill Date Fiscal Period Value]),

    FILTER ('SFDC Opp', 'SFDC Opp'[Parent Account (Anaplan)]  =  Earlier('SFDC Opp'[Parent Account (Anaplan)])),

    FILTER('SFDC Opp','SFDC Opp'[Stage Name]="Billed" || 'SFDC Opp'[Stage Name]="Booked"),

    FILTER('SFDC Opp','SFDC Opp'[Net Rev merged DI]>0))

RETURN

IF('SFDC Opp'[Bill Date Fiscal Period Value] = _minDate,"New","Existing")

However our company rule for New Accounts is if the customer has not purchased in the last 3 years (or since their 1st purchase) then they are classified as a New Account, so in the above example line 3, 4 and 7 should say "New" and line 1, 2, 5 and 6 should say "Existing"

How can I adjust the above formula to carry this calculation out?

Thanks in advance

Atish

[akhatri]

Any help here would be greatly appreciated