Help - Customer Categorization in selected periods

[Anonymous]

Hi, I am new to Power BI and need help on below.

I have data at date and custome level with channel type. I would need to have 2 period(date) paramenters and categorize customers based on selected dates in period parameters. Below is the sample data and expected output.

Customer	Date	Channel		Parameters	From	To
A	1-Oct	On		P1	1-Oct	2-Oct
A	2-Oct	Off		P2	3-Oct	3-Oct
A	3-Oct	Dir
A	4-Oct	On		Output
B	1-Oct	On		P1	P2	Dir	Off	Total
B	2-Oct	Off		On,Off		1	1	2
B	3-Oct	Off		Dir		0	1	1
C	2-Oct	Dir			Total	1	2
C	3-Oct	Off

in the above example, Oct 1 - Oct 2 is selected for P1 (Period 1), so Customer A will fall into On,Off category,Customer B into On,Off and Customer C into Dir for P1. Similar calculation for P2 cateogries and then matrix of these 2 with count of customers. I am not able to find a way to create these calculations, any help is much appreciated.

Thanks in advance.

[v-deddai1-msft]

Hi @Anonymous ，

I can't think of a solution for applying dynamic categeory on column. They must be fixed in the formula based on my knowledge. Maybe someone else could give you a solution.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[v-deddai1-msft]

Hi @Anonymous ，

Would you please try to create two calculated column :

P = SWITCH(TRUE(),'Table'[Date]>=DATE(2020,10,1)&&'Table'[Date]<=DATE(2020,10,2),"P1",'Table'[Date] = DATE(2020,10,3),"P2")

combinations = CONCATENATEX(FILTER('Table','Table'[Customer] = EARLIER('Table'[Customer])&&'Table'[P] = EARLIER('Table'[P])),'Table'[Channel],"&")

Then you can create a measure for count customers in different combinations :

Measure = DISTINCTCOUNT('Table'[Customer])

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EctwMJyl_SlIlaH8QUDWjDkBozN9GGPqSQH4JwBdWSAtGw?e=WIcDJq

If this post help, please consider accept it as the solution to help other member find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft ,

Thank you so much for the help. Calculations looks perfect except that the date filter in the calculation needs to be dynamic based on selection by user. We need 2 date parameters (P1 & P2) for user to select different periods. Then based on these period selection at run time, we need to calculate combinations. Could you please help ..

Thank you.

[Anonymous]

Hi @v-deddai1-msft ,

Thank you so much for your response, but requirement is bit different. I need to divide customers into different sets based on their shopping channel for both period 1 and period 2. For example if customer shopped in both online and offline for the selected period p1, his P1 label need to fixed at customer level to online & offline. I need to group customers into different combinations (Customers shopped on only online, online+offline, only offline etc ..) for both period 1 and period 2 parameters. 

[v-deddai1-msft]

Hi @Anonymous ,

You can create a new calculated column to determine parameter for each customer by date:

parameter = SWITCH(TRUE(),'Table'[Date]>=DATE(2020,10,1) &&'Table'[Date]<=DATE(2020,10,2),"P1",'Table'[Date]=DATE(2020,10,3),"P2")

Then you can create  a matrix for your expected output:

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EV4vA8GQfvZKhJexAEPZeEEBPxh0xugnaN36TCNPOdUCww?e=ccvw1L

If this post help, please consider accept it as the solution to help other member find it more quickly.

Best Regards,

Dedmon Dai