How to Calculate & Count the Number of Customer Visit follow Plan, Unfollow Plan with Order

[ADSL]

Hi BI Team Member,

I would need your help to advise the measure how to calculate and count for:

1. No. of Customer Visit Follow Plan (No Order)

2. No. of Customer Visit Unfollow Plan (Have Order - "E" Order_IND)

3. No. of Customer Visit follow Plan (Have Order - "E" Order_IND)

4. No of Customer Un-Visit (Missing)

Visit_Plan

Daily_Visit

If you want to access and try on the source file, please follow the link -

https://drive.google.com/drive/folders/1h4G65DGngL5oYU6JadAA8PbdyowuaxSn?usp=sharing

Thank you very much!

[jgeddes · Accepted solution]

Try this:

In your Daily_Visit table create a calculated column that tests if the visit was planned for a given day

Planned Visit =

var _wasOnPlan =

LOOKUPVALUE(Customer_Route_Plan[CUST_NAME],Customer_Route_Plan[CUST_CODE],Daily_Visit[CUST_CODE],Customer_Route_Plan[ROUTE_DATE],Daily_Visit[CALL_DATE])

return

IF(

    NOT(ISBLANK(_wasOnPlan)),

    "Yes",

    "No"

)

You can then create the measures

Planned Visit With Order =

CALCULATE(

    DISTINCTCOUNT(Daily_Visit[CUST_CODE]),

    Daily_Visit[Planned Visit] = "Yes" && Daily_Visit[ORDER_IND] = "E"

)

Planned Visit With No Order =

CALCULATE(

    DISTINCTCOUNT(Daily_Visit[CUST_CODE]),

    Daily_Visit[Planned Visit] = "Yes" && Daily_Visit[ORDER_IND] <> "E"

)

Un-Planned Visit With Order =

CALCULATE(

    DISTINCTCOUNT(Daily_Visit[CUST_CODE]),

    Daily_Visit[Planned Visit] = "No" && Daily_Visit[ORDER_IND] = "E"

)

Then create a calculated column in your Customer_Route_Plan table that tests if a visit is missing from the daily route for a given day

Visited Customer =

var _visited =

LOOKUPVALUE(Daily_Visit[CUST_NAME],Daily_Visit[CUST_CODE],Customer_Route_Plan[CUST_CODE],Daily_Visit[CALL_DATE],Customer_Route_Plan[ROUTE_DATE])

return

IF(

    NOT(ISBLANK(_visited)),

    "Yes",

    "No"

)

You can now create the measure

Customer Visit Missing =

CALCULATE(

    DISTINCTCOUNT(Customer_Route_Plan[CUST_CODE]),

    Customer_Route_Plan[Visited Customer] = "No"

)

[ADSL]

Hi @jgeddes,

Sorry...I am replying late to you for this topic.

Actually one customer can visit for many times per month so then we are unique customer count if sales rep visited the same day. If it's not the same day then we count it for them.

So could you please check and advise of your measure? Thanks a lot