Measure question: Specific combination of products within same period of time

[Anonymous]

Hello community members, 

I have a question: 

I would like to know how many times it happens that a client hires the specific combination of 2 products (10B02 and 02A02) during the same period. It is not necessary that the hiring period is completely the same, but there should be at least 1 day overlap. 

Below you find a sample of the data. The last two rows for client BW03 (made them bold) meet the requirements and should therefore 'show up' in the measure.

CLIENT	PRODUCTCODE	STARTDATE	ENDDATE
BW01	10B01	1-1-2022	30-6-2022
BW01	07A02	1-3-2022	1-5-2022
BW02	07A02	1-1-2022	31-3-2022
BW02	10A06	1-1-2022	31-7-2022
BW03	02A02	1-1-2022	1-5-2022
BW03	10B02	1-6-2022	31-10-2022
BW03	02A02	1-11-2022	31-12-2022
BW03	10B02	1-12-2022	15-12-2022

Can anyone help me? 

Hope to hear soon from you! 

Best regards,

Sander

[johnt75 · Accepted solution]

You can create a measure

Ordered at same time = SUMX( VALUES(Orders[CLIENT]),
var product1 = CALCULATETABLE( 
    SELECTCOLUMNS( SUMMARIZE( Orders, Orders[STARTDATE], Orders[ENDDATE]), "Prod1 Start", [STARTDATE], "Prod1 End", [ENDDATE])
    , Orders[PRODUCTCODE] = "10B02")
var product2 = CALCULATETABLE( 
    SELECTCOLUMNS( SUMMARIZE( Orders, Orders[STARTDATE], Orders[ENDDATE]), "Prod2 Start", [STARTDATE], "Prod2 End", [ENDDATE])
    , Orders[PRODUCTCODE] = "02A02")
var allOrders = CROSSJOIN(product1, product2)
return SUMX( allOrders,
    IF( ISEMPTY( INTERSECT( DATESBETWEEN( 'Date'[Date], [Prod1 Start], [Prod1 End]), DATESBETWEEN( 'Date'[Date], [Prod2 Start], [Prod2 End]))), 0, 1)
)
)

This will also count any times when there are multiple overlaps, i.e. a customer hired product 1 and then hired product 2 twice within the period.

[amitchandak]

@Anonymous , is it somthing similar to Market Basket Analysis ?

https://finance-bi.com/power-bi-basket-analysis/
https://businessintelligist.com/2017/03/27/market-basket-analysis-using-power-bi/
https://www.daxpatterns.com/usecases/market-basket-analysis/

[Anonymous]

@amitchandak thanks for your reply. Just looked at the information, but is it is not the answer to my question, because there is no startdate and enddate involved.

I know how to make the measure for; count the number of times someone has product A as well as product B, but i don't know how I need to insert the part of; and the product should be hired at the same time.