Forum Discussion

InsightSeeker's avatar
InsightSeeker
Helper III
10 months ago
Solved

Client Status Measure Based on Selected Date

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: Ex...
Need Help
Show and Tell
  • Praful_Potphode's avatar
    Praful_Potphode
    10 months ago

    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

srlabhe's avatar
srlabhe
Super User
10 months ago
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"
)