Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Number of customers with more than two valid contracts

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

ClientContractDate
C1S12019-12-31
C1S22020-12-31
C2S12020-12-31
C2S22020-12-31
C3S12020-12-31
C3S22021-12-31
C4S22050-03-06
C1S32030-06-30
C5S12020-09-15
C6S12020-01-06
C7S22021-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:

YearC1C2C3C4C5C6C7Number of clients , with more than 2 contracts
201932211113
202032211113
20211 11  10
20221  1   0
20231  1   0
20241  1   0
20251  1   0
20261  1   0
20271  1   0
20281  1   0
20291  1   0
20301  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!

2 ACCEPTED SOLUTIONS

@Anonymous 

 

Try this measure.

 

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

 


Paul

View solution in original post

Change this calc and check

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

View solution in original post

7 REPLIES 7
V-pazhen-msft
Community Support
Community Support

@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)

 

sample test.JPG

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
Not applicable

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.Capture.PNG

 

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

 

 

 

 

Change this calc and check

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

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

Both your solutions works great!

@Anonymous 

 

Try this measure.

 

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

 


Paul

amitchandak
Super User
Super User

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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.