Distinct Count of Product with multiple criteria

[PBI_newuser]

Hi, 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/2022	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)

Sample pbix here. If a product is valid for both "Contract" & Warranty" in the same period, it should count only "Warranty" for that product. If a product is valid for both "Rental" & "Warranty" in the same month, it should count only "Warranty" for that product. How to do so? 

The sum for T&M is incorrect? How to fix it?

[amitchandak]

@PBI_newuser , I think this approach of HT blog with active inactive join should help. What you need is very similar to Active employees

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

[PBI_newuser]

Hi @amitchandak , thank you for your suggestion.

I have created some measures in this sample pbix file. If a product is valid for both "Contract" & Warranty" in the same period, how to count only "Warranty" for that product. (E.g. Product A in 2022-02 and Product D in 2022-03) If a product is valid for both "Rental" & "Warranty" in the same month, how to count only "Warranty" for that product. (E.g. Product B in 2022-02)

Besides, the sum for T&M is incorrect? How to fix it?