Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
@Anonymous
Try this measure.
measure =
CALCULATE(DISTINCTCOUNT('Table'[Client]),FILTER(SUMMARIZE('Table','Table'[Client],"no.contract",[No of Contracts]),[no.contract]>=2))
Paul
Change this calc and check
No of clients = COUNTX(filter(values('Table'[Client]),[No of Contracts]>=2), [No of Contracts])
@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
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:
Change this calc and check
No of clients = COUNTX(filter(values('Table'[Client]),[No of Contracts]>=2), [No of Contracts])
Link
https://www.dropbox.com/s/jrneiipvo28ve3r/Clients%20with%20more%20than%20two.pbix?dl=0
@Anonymous
Try this measure.
measure =
CALCULATE(DISTINCTCOUNT('Table'[Client]),FILTER(SUMMARIZE('Table','Table'[Client],"no.contract",[No of Contracts]),[no.contract]>=2))
Paul
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))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |