Number of customers with more than two valid contracts

[Anonymous]

Hey, 

Need some help with calculation.

I need to calculate by date, how much clients are with more that two valid contracts during particular period (Year/month), and what clients it, is?

The table is simple

Client	Contract	Date
C1	S1	2019-12-31
C1	S2	2020-12-31
C2	S1	2020-12-31
C2	S2	2020-12-31
C3	S1	2020-12-31
C3	S2	2021-12-31
C4	S2	2050-03-06
C1	S3	2030-06-30
C5	S1	2020-09-15
C6	S1	2020-01-06
C7	S2	2021-11-01

There is no start date, so by default it's valid from any min date of Valid date column.

The result should be something like that:

Year	C1	C2	C3	C4	C5	C6	C7	Number of clients , with more than 2 contracts
2019	3	2	2	1	1	1	1	3
2020	3	2	2	1	1	1	1	3
2021	1		1	1			1	0
2022	1			1				0
2023	1			1				0
2024	1			1				0
2025	1			1				0
2026	1			1				0
2027	1			1				0
2028	1			1				0
2029	1			1				0
2030	1			1				0
2031				1				0
2032				1				0
2033				1				0

First column is Year (additionally there should be month also).

Than goes clients with no of active contracts that year.

And last column, summarizes, how much clients there are with multiple contracts.

I just need first and last column (other columns is just for clearance).

Keep in mind that there are lot of data, so solution must operate with more than 500k rows.

Thanks in advance!

[V-pazhen-msft · Accepted solution]

@Anonymous 

Try this measure.

measure = 
CALCULATE(DISTINCTCOUNT('Table'[Client]),FILTER(SUMMARIZE('Table','Table'[Client],"no.contract",[No of Contracts]),[no.contract]>=2))

Paul

[V-pazhen-msft]

@Anonymous 

You could create two columns see below, one is count the how many contracts each client has during the each Year/Month, second one is count the number of clients with 2 or more contracts (I guess you want >=2).

NO.contract/client/month =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Contract] ),
    ALLEXCEPT (
        'Table',
        'Table'[Date].[Year],
        'Table'[Date].[Month],
        'Table'[Client]))


NO.clientwithontract>1 = CALCULATE(COUNT('Table'[Client]),'Table'[NO.contract/client/month]>=2)

Here is the pbix with your sample: 
https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EcwdcSmmUuhOglX_MKO_HVIBpOu0s7tCOSFcB2WxoOUD4Q?e=LgDLhW 

[Anonymous]

Thanks @V-pazhen-msft , @amitchandak  for the replay, I really appretiate that, but unfortunatelly non of your pruposed solution don't give required result.

As I understand there are two levels of calculation:

To calculate virtual table that shows how much valid contrracts have every client for particular date

Calculate how much clients are for that date, wich have 2 or more contracts.

For example in 2019 January there are 8 clients with valid contracts, but just 6 of them have more than 2 contracts.

This is pbix file, and some calculatinons, I really need your help:

https://drive.google.com/open?id=1KYWro1871NFr5dptbCnlhPay76dXq-OG

[amitchandak · Accepted solution]

Change this calc and check

No of clients = COUNTX(filter(values('Table'[Client]),[No of Contracts]>=2), [No of Contracts])

[amitchandak]

Link

https://www.dropbox.com/s/jrneiipvo28ve3r/Clients%20with%20more%20than%20two.pbix?dl=0

[Anonymous]

@amitchandak , @V-pazhen-msft  you, guys rock!

Both your solutions works great!

[V-pazhen-msft · Accepted solution]

@Anonymous 

Try this measure.

measure = 
CALCULATE(DISTINCTCOUNT('Table'[Client]),FILTER(SUMMARIZE('Table','Table'[Client],"no.contract",[No of Contracts]),[no.contract]>=2))

Paul

[amitchandak]

To get the best of the time related calculations. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Try

Current Client = CALCULATE(COUNTx(FILTER(Table, Table[End Date]<max('Date'[Date]))),(Table[client ]))
,CROSSFILTER(Table[ Date],'Date'[Date],None))