How to create measure to calculate which contracts are most likely to follow up each other

[Anonymous]

Dear community,

I have a table with contract information. Each contract is assigned to a client, has a certain type (A-J), and a start and end date. I am trying to analyze which types of contracts are followed up by others, with "follow up" defined by the start date of the 2nd contract being on the same day or later than the end date of the 1st contract for the same client.

To clarify, there's a small data sample in the table below:

Contract ID	Client ID	Contract type	Start date	End date	Duration (days)
24	19	A	Mondayjanuary 1, 2018	Thursday, july 31, 2018	211
26	19	F	Monday, january 1, 2018	Friday, august 11, 2018	242
25	19	A	Saturday, december 1, 2018	Monday, december 31, 2018	30
27	19	F	Thursday, january 3, 2019	Saturday, october 31, 2020	667
28	20	B	Monday, january 1, 2018	Friday, august 11, 2018	242
29	20	G	Thursday, january 3, 2019	Saturday, october 31, 2020	667

Contract 24 ends on 31/7/2018, and is thus followed up by contracts 25&27

Contract 25 ends on 31/12/2018, and is thus followed up by contract 27

Contract 28 ends on 11/08/2018, and is thus followed up by contract 29

If contracts are for different customers, there's no "follow up" (so 24 is not followed up by 29)

My set includes about 28,905 contracts. What I'm trying to get as end result is a matrix showing which shows a count of which contract types are followed up by others (horizontal = reference type, vertical = "followed up by". For the sample dataset above, the outcome should be:

Reference	A	B	F	G
A	1		2
B				1
F	1		1
G

The problem is thus a bit similar to basket analysis, but different as it does not analyze which contracts run simoultaneously but which contracts follow up each other. 

I've uploaded a pbix file with the dataset here: https://drive.google.com/drive/folders/11tA7ArjlLgGVPSf2Zn2z93ONH-8x-wtT?usp=sharing

Could somebody point me in the right direction, please?

[DataInsights · Accepted solution]

@Anonymous,

Try this solution:

1. Create a calculated table based on your data table (FactTable in this example). This table should not have a relationship with your data table.

ExpandedFactTable = 
GENERATE (
    FactTable,
    VAR vClient = FactTable[Client ID]
    VAR vEndDate = FactTable[End date]
    VAR vFollowUpContracts =
        FILTER (
            FactTable,
            FactTable[Client ID] = vClient
                && FactTable[Start date] >= vEndDate
        )
    VAR vFollowUpContractType =
        SELECTCOLUMNS (
            vFollowUpContracts,
            "Follow up Contract Type", FactTable[Contract type]
        )
    RETURN
        vFollowUpContractType

2. Create measure:

Contract Count = COUNT ( ExpandedFactTable[Contract ID] )

3. Create matrix using columns in ExpandedFactTable and measure [Contract Count]:

---

I would be interested to see if this can be done with only measures (and not a calculated table). Here are some links that may be useful:

https://community.powerbi.com/t5/Desktop/Basket-Analysis-with-Power-BI/m-p/649017

https://blog.enterprisedna.co/advanced-basket-analysis-example-in-power-bi-cross-selling/ 

[Anonymous]

Perfect! This solved the problem. Thanks for you time and help😀