Below is a formula that may help with determining 'new' customers in a fiscal year. Based on your setup, you'll probably need to tweak some. Hopefully, you can leverage based on your environment.
This is based on two tables in my app: Customers and Visits (where customers have many visits).
- Create application variable to set the first month of the fiscal year ([Fiscal Year Start Month]
- Add Formula - text field to Visits table (code is below and I've broken it up to help explain the different components (Field 56 is Related Customer in Visits table)
If visitsBeforeThisVisit is 0, that visit is the first visit of the fiscal year. "New" is returned if visitsBeforeThisVisit is 0.
//Calculates Fiscal Year Start Based on Vist Date
var date FYStart = If(Month( ToDate([Visit Start Date & Time]) ) < ToNumber([Fiscal Year Start Month]),
Date(Year(ToDate([Visit Start Date & Time]))-1,ToNumber([Fiscal Year Start Month]),1),
Date(Year(ToDate([Visit Start Date & Time])),ToNumber([Fiscal Year Start Month]),1));
//Components of Query
var text qryCustomer = "{56.EX.'" & [Pet - Related Customer] & "'}";
var text qryFYStart = "{6.OAF.'" & $FYStart & "'}";
var text qryCurrentVisit = "{6.BF.'" & [Visit Start Date & Time] & "'}";
//Retrieves Number of Visits in FY before the record's visit start date
var number visitsBeforeThisVisit = Size(GetRecords($qryCustomer & "AND" & $qryCurrentVisit & "AND" & $qryFYStart));
//Returns New if visit is the first visit of the fiscal year for the customer
If($visitsBeforeThisVisit = 0, "New","")