Calculated Column identifying Aged Category

[Anonymous]

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!

[technolog · Accepted solution]

To accurately categorize partners as "New" or "Existing" based on the start of the prior quarter, you need to create a calculated column that dynamically calculates the partner's status based on the previous quarter's start date. Here’s how you can achieve this:

Step 1: Calculate Partner Start Date
You already have this calculated column:

Partner Start Date = CALCULATE(
MIN('DW FactLead'[LeadCreatedDate]),
FILTER(
'DW DimPartner',
'DW DimPartner'[PartnerName] = EARLIER('DW DimPartner'[PartnerName])
)
)
Step 2: Determine the Start of the Prior Quarter
Create a calculated column to determine the start date of the prior quarter:

StartOfPriorQuarter =
VAR CurrentDate = TODAY()
VAR CurrentQuarter = QUARTER(CurrentDate)
VAR CurrentYear = YEAR(CurrentDate)
VAR PriorQuarterStartDate =
SWITCH(
TRUE(),
CurrentQuarter = 1, DATE(CurrentYear - 1, 10, 1),
CurrentQuarter = 2, DATE(CurrentYear, 1, 1),
CurrentQuarter = 3, DATE(CurrentYear, 4, 1),
CurrentQuarter = 4, DATE(CurrentYear, 7, 1)
)
RETURN PriorQuarterStartDate
Step 3: Categorize Partner Age Based on Prior Quarter
Create a calculated column to categorize partners as "New" or "Existing" based on whether their start date is within 12 months before the start of the prior quarter:

Partner Age Category =
VAR PriorQuarterStart = 'DW DimPartner'[StartOfPriorQuarter]
VAR TwelveMonthsAgo = PriorQuarterStart - 365
RETURN
IF(
'DW DimPartner'[Partner Start Date] >= TwelveMonthsAgo,
"New",
"Existing"
)
Dynamic Dashboard for QTD and Prior Quarter
To create a dynamic dashboard that shows both QTD and Prior Quarter results, you can use measures and time intelligence functions in DAX.

Step 4: Create Measures for QTD and Prior Quarter
Create measures to calculate metrics for the current quarter to date (QTD) and the prior quarter.

Example Measure for QTD:
QTD_Metric =
CALCULATE(
[YourMetric],
DATESQTD('Date'[Date])
)
Example Measure for Prior Quarter:
PriorQuarterMetric =
CALCULATE(
[YourMetric],
PREVIOUSQUARTER('Date'[Date])
)
Step 5: Add Slicers and Visuals