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:

  • 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!

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

4 Replies

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    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"
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    Hi InsightSeeker 

    Thanks for reaching out to the Microsoft fabric community forum.

     

    Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by srlabhe  and  Praful_Potphode  . If you still require support, please let us know, we are happy to assist you.

     

    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    Hi InsightSeeker ,

     

    We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
    If you still require support, please let us know, we are happy to assist you.

     

    Thank you.