Days between purchase and cancellation (days between two rows)

[Anonymous]

Hi all, 

I want to create a measure that calculates the number of days between a customer buys a product and returns it (if that happens). 

Our sales are stored in rows in a table like the one below:

Every bought product is stored in individual rows and has an unique purchase ID.  Can anyone help to count the number of days between the pruchase date and the return date?

Best,

NicoM96

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here's my solution.

Create a measure.

Measure =
IF (
    MAX ( 'Table'[Purchase/return] ) = "return",
    DATEDIFF (
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Product] = MAX ( 'Table'[Product] )
                    && 'Table'[Purchase ID] = MAX ( 'Table'[Purchase ID] )
                    && 'Table'[Date] <= MAX ( 'Table'[Date] )
                    && 'Table'[Purchase/return] = "Purchase"
            ),
            'Table'[Date]
        ),
        MAX ( 'Table'[Date] ),
        DAY
    ),
    BLANK ()
)

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whitewater100]

Hi:

If you want to see positive value for answer it would be:

Return Days =

var ifreturn =

CALCULATE(MAX(Purchases[Date]), ALLEXCEPT(Purchases,Purchases[Purch ID]))

return

IF(Purchases[Date] = ifreturn, BLANK(), INT(Purchases[Date] - ifreturn))*-1

[Whitewater100]

Hi Nico:

I was calling your table "Purchases.

I added Calc col

Return Days =

var ifreturn =

CALCULATE(MAX(Purchases[Date]), ALLEXCEPT(Purchases,Purchases[Purch ID]))

return

IF(Purchases[Date] = ifreturn, BLANK(), INT(Purchases[Date] - ifreturn))

I hope you can mark as solved. Thanks..