Hi all -
Hoping someone can help me better formulate a categorization I'm trying to establish based on the start date of some of our partner accounts.
What I am trying to accomplish -
I have certain objectives for my team based on New vs. Existing partners. A New partner would be one that has joined our services within the last 12 months. Existing would be any partner who has been on our services for 12 months or more. This is pretty straightforward.
Challenge -
However, when the calendar quarter ends, we populate our metrics and bonus our employees on their performance. So I'm really interested at knowing if the Partner was New or Existing at the Start of the Prior Quarter; the reason I'm interested in Prior Quarter is because I'm typically running these metrics after Quarter end. Ideally I'd love a Dynamic dashboard that could show QTD results as well as Prior Quarter results, but I think that will just need to be accomplished with 2 different reports.
What I've done so far -
I created a calculated column to find the earliest Lead submission date for each partner:
Partner Start Date = CALCULATE(MIN('DW FactLead'[LeadCreatedDate]),FILTER('DW DimPartner','DW DimPartner'[PartnerName]=EARLIER('DW DimPartner'[PartnerName])))
I then created a calculated column to identify their Category (New vs. Existing)
Partner Age Category = IF('DW DimPartner'[Partner Start Date]<TODAY()-365-90,"Existing","New")
As you can probably see, I'm adjusting the 12 month rule to include an additional 90 days to try and accomodate the quarter currently. But clearly this isn't ideal. Any help would be greatly appreciated!
