count monthly not from single customers with valid contracts based on start and end date

[Syndicate_Admin]

Hello everyone

I'm new to power bi and have a problem with the following problem:

I have a table with customer name, contract number, start date, and contract date.

In this table you might have more than one valid contract per customer, or consecutive validity contracts per customer, or some without a valid contract.

1. I want to be able to create a matrix view that is counting unique customers with active contracts on a monthly basis

2. Another matrix with customers lost on monthly bases

2. Another matrix with profit customers on a monthly basis

Input table :

Customer name	Contract not	valid from		new valid for (end of month)
A	1/2018	01.04.2018		31.03.2019
A	6/2019	01.04.2019		31.03.2020
A	11/2020	01.04.2020		31.03.2021
B	3/2017	01.02.2017		31.12.2018
B	8/2019	01.04.2019		31.03.2020
B	8/2020	01.04.2020		31.03.2021
C	10/2016	01.11.2016		31.10.2018
C	10/2018	01.01.2018		28.02.2019
C	10/2019	01.11.2019		31.10.2020
D	4/2016	01.01.2017		31.12.2018
D	5/2018	01.01.2019		31.12.2019
D	6/2018	01.01.2019		31.12.2019
D	7/2019	01.01.2020		31.12.2020
she	1 /2015	01.04.2016		31.12.2017
she	2 /2015	01.03.2016		31.12.2017
she	7 /2015	01.03.2016		31.12.2017
she	4/2017	01.01.2018		31.12.2018
she	5/2017	01.01.2018		31.12.2018
she	6/2017	01.01.2018		31.12.2018
she	20/2018	01.01.2019		31.12.2020
she	21/2018	01.01.2019		31.12.2020
she	22/2018	01.01.2019		31.12.2020
she	30/2018	01.01.2019		31.12.2020
she	35/2018	01.01.2019		31.12.2020
she	39/2018	01.01.2019		31.12.2020

result:

1. matrix1 : not of customers with active contract (I have created a table only with 5 monts)

	2018	2019	2019	2019	2019
matrix 1	December	January	February	March	April
A	1	1	1	1	1
B	1				1
C	1	1	1
D	1	1	1	1	1
she	1	1	1	1	1

2: No lost customers

	2018	2019	2019	2019	2019
matrix 2	December	January	February	March	April
A
B		1
C				1
D
she

3. No new customers

	2018	2019	2019	2019	2019
matrix 3	December	January	February	March	April
A
B					1
C
D
she

Thank you.

[v-luwang-msft]

Hi @Syndicate_Admin ，

You said ,you have a table with column customer name, contract number, start date, and contract date,but in the below ,I see the column   Customer name, Contract not, valid from ,new valid for (end of month).

Also can you give an example of how to get the output table from the input table?

Best Regards

Lucien