Client Status Measure Based on Selected Date

[InsightSeeker]

Hello,

I need assistance creating a measure that dynamically calculates client status based on a date selection in my report.

I need to classify each client into these status categories:

• Existing Client: Any client trading for more than 12 months
• NBTO: Any client trading for less than or equal to 12 months
• New Client: Any client trading for 0 months
• Lost Client: Any client NOT traded for more than 6 months from the current date

I've attached sample data to demonstrate the scenario. Since the status needs to update based on the date selected in my report (not a fixed date), I need this as a measure rather than a calculated column.

Sample Data 

Can someone help me create the appropriate measure for this dynamic classification?

Thank you!

[srlabhe]

These helper measures will find the first and last trade dates for each client based on the date range selected by the user. 

MaxSelectedDate
This measure retrieves the latest date the user has selected in the report's date slicer. 

dax

MaxSelectedDate = MAX('Dates'[Date])

ClientFirstTradeDate
This measure finds the earliest transaction date for each client. 

dax

ClientFirstTradeDate = 
CALCULATE(
    MIN('Trading Data'[TransactionDate]),
    ALLEXCEPT('Trading Data', 'Trading Data'[ClientID])
)

ClientLastTradeDate
This measure finds the most recent transaction date for each client. 

dax

ClientLastTradeDate = 
CALCULATE(
    MAX('Trading Data'[TransactionDate]),
    ALLEXCEPT('Trading Data', 'Trading Data'[ClientID])
)

Step 3: Create the dynamic client status measure

This is the core DAX measure that will determine each client's status by checking the conditions against the MaxSelectedDate. 

ClientStatus

dax

ClientStatus = 
VAR _SelectedDate = [MaxSelectedDate]
VAR _FirstTradeDate = [ClientFirstTradeDate]
VAR _LastTradeDate = [ClientLastTradeDate]
VAR _MonthsSinceFirstTrade = DATEDIFF(_FirstTradeDate, _SelectedDate, MONTH)
VAR _MonthsSinceLastTrade = DATEDIFF(_LastTradeDate, _SelectedDate, MONTH)
RETURN
SWITCH(
    TRUE(),
    -- Condition for Lost Client: Not traded for more than 6 months
    _MonthsSinceLastTrade > 6, "Lost Client",
    -- Condition for New Client: First trade is in the selected period (0 months of trading)
    _MonthsSinceFirstTrade = 0, "New Client",
    -- Condition for NBTO (New But Trading Once): Traded for <= 12 months
    _MonthsSinceFirstTrade <= 12, "NBTO",
    -- Condition for Existing Client: Traded for > 12 months
    _MonthsSinceFirstTrade > 12, "Existing Client",
    "Inactive"
)

[Praful_Potphode]

Hi @InsightSeeker 
Try below measure

Status = 

var max_month=MAX('Calendar Ultimate'[Date])
//trading dates for last 13 months
var existing_client_dates=DATESINPERIOD('Calendar Ultimate'[Date],max_month,-13,MONTH)
//trading dates for last 12 months
var nbto_dates=DATESINPERIOD('Calendar Ultimate'[Date],max_month,-12,MONTH)

//trading dates for last 6 month
var lost_client_dates=DATESINPERIOD('Calendar Ultimate'[Date],TODAY(),-6,MONTH)

var res_existing_client=CALCULATE(COUNT(data[Order_Number]),existing_client_dates)
var res_newclient=CALCULATE(COUNT(data[Order_Number]),'Calendar Ultimate'[Date]=max_month)
var res_nbto=CALCULATE(COUNT(data[Order_Number]),nbto_dates)
var res_lost_client=CALCULATE(COUNT(data[Order_Number]),lost_client_dates)

RETURN 
SWITCH(
    TRUE(),
    NOT ISBLANK(res_existing_client),"Existing CLient", //more than 12 months
    NOT ISBLANK(res_nbto),"NBTO",   //less than equal to 12 months
    NOT ISBLANK(res_newclient),"NBTO", //zero month
    ISBLANK(res_lost_client) || res_lost_client=0,"Lost CLient" //more than 6 month

)

please give kudos or mark it as resolved once confirmed