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

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