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