Time slots in months between rows in different categories

[Anonymous]

Hello all,

I need help in finding a solution to calculate the time slots in months between 2 products purchased by the same customer. I have lots of customers, but only 3 categories of products: apples, pears and bananas and some missing values in PurchasedDate column.

CustomerID	Product	PurchasedDate
1	apples	27/09/2017
1	pears	04/09/2019
2	apples	26/09/2018
2	pears	01/10/2020
2	bananas	26/11/2020
3	apples
3	pears	25/05/2020

I have to calculate a measure that shows:

Customer 1 has bought pears vs apples: >12 months

Customer 2 has bought bananas vs pears: 0-3 months

Customer 3 has bought pears vs apples: Not defined

The time slots I'm interested in are:

0-3 m

3-6 m

6-9 m

9-12

>12

Not defined

Any idea is very helpful for me and much appreciated.

Thanks in advance!

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Please use below measure:

Measure =
VAR a =
    CALCULATE (
        MAX ( 'Table (2)'[PurchasedDate] ),
        FILTER (
            ALL ( 'Table (2)' ),
            [CustomerID] = MAX ( 'Table (2)'[CustomerID] )
                && [Product] <> MAX ( 'Table (2)'[Product] )
                && [PurchasedDate] <= MAX ( 'Table (2)'[PurchasedDate] )
        )
    )
VAR b =
    DATEDIFF ( a, MAX ( 'Table (2)'[PurchasedDate] ), MONTH )
RETURN
    IF (
        ISBLANK ( a ),
        "NotDefined",
        IF (
            b <= 3,
            "0-3 m",
            IF (
                b <= 6,
                "3-6 m",
                IF ( b <= 9, "6-9 m", IF ( b <= 12, "9-12 m", IF ( b > 12, ">12" ) ) )
            )
        )
    )

Pbix attached.

[amitchandak]

@Anonymous , Create a new column like

new column = 
var _month = datediff([PurchasedDate], maxx(filter(Table,[CustomerID]=earlier([CustomerID]) && [PurchasedDate] < earlier([PurchasedDate])),[PurchasedDate]), month)

return 
switch( true(),
_month <=3 , "   0-3 m",
_month <=6 , "  3-6 m",
_month <=9 , "  6-9 m",
_month <=12 , "  9-12 m",
_month >12 , ">12",
"Not defined"
)