How to calculate distinct count of product by considering start date and end date

[PBI_newuser]

Hi, I want to calculate Contract Pentration in each month but not sure how to create such measure. 

• Contract Penetration = Products under Contract / Total Active Products 

One product can more than one contract as it renews after the contract is expired. Some products don't renew contract after it expired. 

For example, in 2021-01, Product A,B,D are installed before or on 2021-1 and hence total active products = 3.

In 2021-02, product A,B,D,E are installed before or on 2021-02 and hence the total active products = 4. But in 2021-02, Product E's contract starts from 2021-03 so Products under Contract = 3.

In 2021-09, Product B has no renew contract. Only Product A and E has active contracts. Hence the Products under Contract = 2.

Product	Contract Number	Start Date	End Date	Install Date
A	CN-111	08/17/2021	08/16/2022	01/10/2019
A	CN-222	08/17/2020	08/16/2021	01/10/2019
A	CN-333	08/17/2019	08/16/2020	01/10/2019
B	CN-444	09/16/2015	09/15/2020	05/11/2009
B	CN-555	09/16/2020	08/31/2021	05/11/2009
C	CN-666			08/03/2021
D	CN-777	12/01/2019	03/30/2021	05/09/2019
E	CN-888	03/10/2021	03/09/2022	02/13/2021

Expected Result:

Month	Products under Contract	Total Active Products	Contract Penetration
2021-01	3	3	100%
2021-02	3	4	75%
2021-03	4	4	100%
2021-04	3	4	75%
2021-05	3	4	75%
2021-06	3	4	75%
2021-07	3	4	75%
2021-08	3	5	60%
2021-09	2	5	40%
2021-10	2	5	40%
2021-11	2	5	40%
2021-12	2	5	40%

[amitchandak · Accepted solution]

@PBI_newuser , refer if the attached file can help

[Jihwan_Kim · Accepted solution]

Hi,

I suggest having a calendar table like below.

Please check the below picture and the attached pbix file.

Products under contract: = 
IF (
    HASONEVALUE ( 'Calendar'[Year-Month] ),
    COUNTROWS (
        SUMMARIZE (
            FILTER (
                Data,
                Data[Start Date] <= MAX ( 'Calendar'[Date] )
                    && Data[End Date] >= MIN ( 'Calendar'[Date] )
            ),
            Data[Product]
        )
    )
)

Products Active: = 
IF (
    HASONEVALUE ( 'Calendar'[Year-Month] ),
    COUNTROWS (
        SUMMARIZE (
            FILTER ( Data, Data[Install Date] <= MAX ( 'Calendar'[Date] ) ),
            Data[Product]
        )
    )
)

Ratio measure: = 
DIVIDE ( [Products under contract:], [Products Active:] )

[amitchandak · Accepted solution]

@PBI_newuser , refer if the attached file can help