How to calculate distinct count of product with criteria

[PBI_newuser]

Hi, I want to calculate Contract Pentration in each month with the below criteria.

• Contract Penetration = Distinct Count of Product under Contract/Total Installs
• If the product has contract under Warranty, do not count it for both Contracted and Install. E.g Product A is under warranty (CN-222) from Aug-2020 to Aug-2021, hence exclude Product A from the sum of "Contracted" and "Install" from Aug-2020 and Aug-2021. Include Product A in the distinct count from Aug-2021 to Aug-2022 for "Contracted" and "Install".
• If the product has contract under Rental, exclude it from both "Contracted" and "Install". E.g Product B is include in the count for "Contracted" from Sep-2015 to May-2021 and for "Install" from May-2009 to May-2021. Exclude Product B from May-2021 to Aug-2022.
• If the product has no contract, then count it under "Install" from the Install Date. E.g Product C is included in the count for "Install" from Aug-2021.
• If the product has Contract Type = Demo, exclude it from calculation.

Product	Contract Number	Start Date	End Date	Install Date	Contract Type
A	CN-111	08/17/2021	08/16/2022	01/10/2019	Purchased
A	CN-222	08/17/2020	08/16/2021	01/10/2019	Warranty
A	CN-333	08/17/2019	08/16/2020	01/10/2019	Warranty
B	CN-444	09/16/2015	05/15/2021	05/11/2009	Purchased
B	CN-555	05/16/2021	08/31/2022	05/11/2009	Rental
C	CN-666			08/03/2021
D	CN-777	12/01/2019	03/30/2021	05/09/2019	Demo
E	CN-888	03/10/2021	03/09/2022	02/13/2021	Purchased
F	CN-999	04/17/2020	04/16/2021	01/10/2019	Warranty
F	CN-000	04/17/2021	04/16/2022	01/10/2019	Purchased
G	CN-121	05/22/2021	05/21/2022	02/10/2021	Purchased

Expected Result:

Month	Contracted	Install	Contract Penetration %
2021-01	1	1	100%
2021-02	1	3	33%
2021-03	2	3	67%
2021-04	3	4	75%
2021-05	4	4	100%
2021-06	3	3	100%
2021-07	3	3	100%
2021-08	4	5	80%
2021-09	4	5	80%
2021-10	4	5	80%
2021-11	4	5	80%
2021-12	4	5	80%

[lbendlin]

Your expected result has month level granularity. Your source data has day level granularity. Should only complete months be considered, or should the entire month be considered even if the date may be the last day of the month? Or do you want to prorate it?

[PBI_newuser]

Hi @lbendlin , I wanted to count the product based on the type defined below:

• Rental: Count the product if it has purchased any contract with contract term Rental. E.g. Product C has active "Rental" contract in 2022-02 and 2022-03. Product B has active "Warranty" in 2022-02 and hence the "Rental" contract shouldn't count here. Product B should be included in the count in 2022-03.
• Contract: Count the product if there is any "Purchased" contract active in the month. E.g. Product H has active "Purchased" contract in 2022-02 and Product A has active "Purchased" contract in 2022-03.
• Warranty: Count the product if there is active "Warranty" in the month. E.g. Product A has active "Warranty" in 2022-02 and has active "Purchased" contract in 2022-03. Same goes to B,D,G.
• T&M: Count the product if they don't have contract or expired contracts. E.g. Product E has installed after 2021-02 and has no contract, it's considered T&M. Product F has expired contract in 2022-02. Product H has expired contract in 2022-03 but active contract in 2022-02. Product I is installed in 2022-03 and it has no contract.

Each product should be counted as one in each month. How to create such measure?

Product	Term	Contract Start Date	Contract End Date	Installed Date
A	Purchased	02/13/2022	01/12/2023	01/10/2019
A	Warranty	1/13/2021	02/12/2022	01/10/2019
B	Rental	02/23/2022	02/22/2026	05/11/2009
B	Warranty	02/23/2021	02/22/2022	05/11/2009
C	Rental	05/01/2021	03/28/2022	05/09/2019
C	Rental	09/29/2020	04/30/2021	05/09/2019
D	Warranty	3/31/2021	03/30/2022	05/09/2019
D	Purchased	3/31/2022	03/30/2026	05/09/2019
E				02/13/2021
F	Purchased	01/13/2021	01/12/2022	01/10/2019
F	Warranty	1/13/2020	01/12/2021	01/10/2019
G	Warranty	01/13/2021	03/12/2022	01/10/2019
H	Purchased	12/13/2020	02/12/2021	01/10/2019
I				03/13/2022

Expected Output:

Type	2022-02	2022-03
Rental	1 (C)	2 (B,C)
Contract	1 (H)	1 (A)
Warranty	4 (A,B,D,G)	2 (D,G)
T&M	2 (E,F)	4 (E,F,H,I)

[lbendlin]

As I mentioned before your data has date level granularity but your expected result is on month level.  So this statement 

Contract: Count the product if there is any "Purchased" contract active in the month. E.g. Product H has active "Purchased" contract in 2022-02 and Product A has active "Purchased" contract in 2022-03.

is ambiguous as Product H does not have a contract for the dates in February after Feb 12. That means you cannot really show anything on month level unless you define what "in a month"  means for you. Should the whole month count even if the contract ends on the first day of the month?