count monthly no of unique customers with valid contracts based on start and end date

[URobert]

Hi all,

I am new in power bi and I have a problem with the below issue :

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

In this table I could 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 which is counting unique customers with active contracts on a monthly bases

2. Another matrix with lost customers on a monthly bases

2. Another matrix with gain customers on a monthly bases

Input table :

Customer name	Contract no	valid from		new valid to (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
EA	1 /2015	01.04.2016		31.12.2017
EA	2 /2015	01.03.2016		31.12.2017
EA	7 /2015	01.03.2016		31.12.2017
EA	4/2017	01.01.2018		31.12.2018
EA	5/2017	01.01.2018		31.12.2018
EA	6/2017	01.01.2018		31.12.2018
EA	20/2018	01.01.2019		31.12.2020
EA	21/2018	01.01.2019		31.12.2020
EA	22/2018	01.01.2019		31.12.2020
EA	30/2018	01.01.2019		31.12.2020
EA	35/2018	01.01.2019		31.12.2020
EA	39/2018	01.01.2019		31.12.2020

result :

1. matrix1 : no of customers with active contract (I've created a table only with 5 monts)

	2018	2019	2019	2019	2019
matrix 1	dec	jan	feb	march	april
A	1	1	1	1	1
B	1				1
C	1	1	1
D	1	1	1	1	1
EA	1	1	1	1	1

2: no of customers lost

	2018	2019	2019	2019	2019
matrix 2	dec	jan	feb	march	april
A
B		1
C				1
D
EA

3. no of new customers 

	2018	2019	2019	2019	2019
matrix 3	dec	jan	feb	march	april
A
B					1
C
D
EA

Thank you.

[v-lili6-msft]

hi @URobert 

Create three measure as below:

no of customers with active contract = CALCULATE(DISTINCTCOUNT('Table'[Customer name]),FILTER('Table','Table'[valid from]<=MAX('Date'[Date])&&'Table'[new valid to]>=MAX('Date'[Date])))

no of customers lost = 
var _premonthvalue=CALCULATE(DISTINCTCOUNT('Table'[Customer name]),FILTER('Table','Table'[valid from]<=MAXX(DATEADD('Date'[Date],-1,MONTH),'Date'[Date])&&'Table'[new valid to]>=MAXX(DATEADD('Date'[Date],-1,MONTH),'Date'[Date])))
return
IF([no of customers with active contract]=BLANK()&&_premonthvalue>0,_premonthvalue)

no of new customers = 
var _premonthvalue=CALCULATE(DISTINCTCOUNT('Table'[Customer name]),FILTER('Table','Table'[valid from]<=MAXX(DATEADD('Date'[Date],-1,MONTH),'Date'[Date])&&'Table'[new valid to]>=MAXX(DATEADD('Date'[Date],-1,MONTH),'Date'[Date])))
return
IF([no of customers with active contract]>0&&_premonthvalue=BLANK(),[no of customers with active contract])

and here is sample pbix file, please try it.

By the way, you'd better enable show Items with no data for date field in matrix

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

Regards,

Lin

[URobert]

Hi @v-lili6-msft

Thank you for your answer.

I coudn't open your file, but I've used your formula from your answer.

The solutution for point 1 (count no of customers with active contracts) it is working great.

I have some issued with formulas for "no of customers lost" and "no of new customers" -> if I count them based on the customer name it is ok, but if I want to cumulate them on monthly bases, doesn't show me as totals

For no of customers lost I want to show as a total on monthly bases, sum of customers which had an active contract last month and doesn't have an active contract this month

For no of new customers I want to show as a total on monthly bases, sum of customers which hadn't an active contract last month but they have an active contract this month.

Thank you in advance !

[v-lili6-msft]

hi @URobert 

For 2no of customers lost and 3no of new customers , Could you please explain it based on the above sample data, I'm a little confused by your description. that will be a great help.

Regards,

Lin

[URobert]

Hi v-lili6-msft,

Thank you for your reply,

I can't upload the pbx file, so I'll try to explan here, in more detail for point 2 and 3.

I've created a table with all the counts (active, new and lost customers).

Total unique count for no of new customers and no of lost customers shoud look like this :

- based on year and month :

- based on year :

Please find below also the data colored :

- yellow -> new customer

- oragne -> lost customer (temporary)

- green -> new customer (gain an old customer)

- blue -> lost customer

Thank you in advance !

[v-lili6-msft · Accepted solution]

hi  @URobert 

Ok, I have the adjust all these formula:

no of new customers = 
var _premonthvalue=CALCULATE(DISTINCTCOUNT('Table'[Customer name]),FILTER('Table','Table'[valid from]<=MAXX(DATEADD('Date'[Date],-1,MONTH),'Date'[Date])&&'Table'[new valid to]>=MAXX(DATEADD('Date'[Date],-1,MONTH),'Date'[Date])))
return
IF(([no of customers with active contract]>0&&_premonthvalue=BLANK())||([no of customers with active contract]>_premonthvalue),1)

new customers = 
var _table=SUMMARIZE('Date','Date'[Year],'Date'[Month],"_value",[no of new customers]) return
SUMX(_table,[_value])

no of customers lost = 
var _premonthvalue=CALCULATE(DISTINCTCOUNT('Table'[Customer name]),FILTER('Table','Table'[valid from]<=MAXX(DATEADD('Date'[Date],-1,MONTH),'Date'[Date])&&'Table'[new valid to]>=MAXX(DATEADD('Date'[Date],-1,MONTH),'Date'[Date])))
return
IF(([no of customers with active contract]=BLANK()&&_premonthvalue>0)||([no of customers with active contract]<_premonthvalue),1)

lost customers = 
var _table=SUMMARIZE('Date','Date'[Year],'Date'[Month],"_value",[no of customers lost]) return
SUMX(_table,[_value])

and here is new sample pbix file, please try it.

Regards,

Lin

[URobert]

Hi v-lili6-msft,

Thank you very much for your solution !!!

I was also able to download your file :).

The only issue that I have is that the new valid to date is the first day of next month and is counting the customer as lost in the new month and is realy lost in the previos month.

Thank you again for your big help !

[amitchandak]

@URobert , My blog on a similar line, Active is Current Employee there

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...