How to Calculate and Count the Number of Time Visit per Month of Customer

[ADSL]

Dear BI Community Support Team,

Sales Rep have a customer routing plan to visit the customer for monthly as the link and screenshot below.

Source: https://drive.google.com/drive/folders/10WygsxRAbphvkZe_jvXTcdfehcb2l6NU?usp=sharing 

Please kindly advise the measure calculation to identify the number of customer visit, total visit plan and time visit per week & month as the format below.

Best Regards,

[lbendlin]

Please define what you understand by "week".  What's the start day of the week?

[ADSL]

Hi @lbendlin ,

The purpose of report/topic, we want to identify the visit routing plan that sales rep visit the customer by month. So then I think we can simplify it as the following below:

- If we count the customer code and result showing 01 --- mean visit 01 time per month

- If we count the customer code and result showing 02 --- mean visit 02 time per month

- If we count the customer code and result showing 03 --- mean visit 03 time per month

Any suggestion of the measure calculation? If we can identify the visit time per month, can we group it into week1, week2, week3, week4 & week5?

[lbendlin]

If we can identify the visit time per month, can we group it into week1, week2, week3, week4 & week5?

No, weeks and months are incompatible.  You need to calculate the "once a week" metric separately.

When you say "sales rep visits customer once a week" do you mean every week? when does the interval start, when does it end? What if the sales rep visits more than once a week, should it not be counted?

[ADSL]

Hi @lbendlin,

Yes, agreed that metric must separate for Week & Month. Actually we count it all for them that we call the # of time different in the week and month.

In Week (visit plan in the same week)

> Day 01 Week01 --- called 1 time per week

> Day 02 Week01 --- called 2 time per week

-

-

> Day 07 Week01 --- called 7 time per week

In Month ===> If it's over 2 times in different week then we call # of time in month not week.

> 2 times per month

> 3 times per month

> 4 times per month 

If it's not working and cannot apply, Any suggest or solution that we can track the visit plan of sales rep?

Best Regards,

[lbendlin]

Assuming that every customer has been visited at least twice then the average number of days between visits seems to be a more meaningful metric.

[ADSL]

Hi @lbendlin,

Any suggestion/concern? If we count the visit and follow the grouping below:

1. 1 Time

2. 2 Time

3. 3 Time

4. 4 Time

5. > 4 Times

These grouping above is not focus on week or month, just have the number of visit time.

Best Regards,

[ADSL]

Hi @Greg_Deckler,

Could you please kindly check and suggestion of this request? 

Thanks and Regards,

[Greg_Deckler]

@ADSL I have similar thoughts and questions as @lbendlin . However, per the original ask and my understanding of it, perhaps this is a start (see attached PBIX below signature)

Measure = 
    VAR __Table = SUMMARIZE('CUST_ROUTE_PLAN',[SALESREP_CODE],[CUST_NAME],[Month],"__Count",COUNTROWS('CUST_ROUTE_PLAN'))
RETURN
    SWITCH(MAX('Table'[Column1]),
        "1 Time/Month",COUNTROWS(FILTER(__Table,[__Count] = 1))+0,
        "2 Time/Month",COUNTROWS(FILTER(__Table,[__Count] = 2))+0,
        "3 Time/Month",COUNTROWS(FILTER(__Table,[__Count] = 3))+0,
        "4 Time/Month",COUNTROWS(FILTER(__Table,[__Count] = 4))+0,
        COUNTROWS(FILTER(__Table,[__Count] > 4))+0
    )

[ADSL]

Hi @Greg_Deckler ,

Thank you for your helpful feedback.

1. I have been checked your file and mapping with excel and requirement then found.

Please kindly help!

2. For ('Table'[Column1]), If we want to separate it then what is the measure? In your file, it didn't have.

Best Regards,