Subtract current row from subsequent row

[Anonymous]

Hi,

I was doing a churn analysis for my customers.
I have a list of data with customers and their purchasing date and I would like to know how many of them come back after a certain period of no show e.g. 30days.

This is the data I have:

And I would like to calculate the duration of no show by subtracting the previous purchasing date using the subsequent purchasing date. The cut off date will be on 31st May 2018, which the last purchasing date will be minus by. 

I will later filter customer with duration of no show more than 30days to see whether they have subsequent purchasing record after the long duration of no show, by seeing whether there is any numbering more than the filtered numbering.

Can someone assist on how should I carry this out? Or you may provide me a better solution for this.

[Anonymous]

Not sure if I understand your requirement correctly.

You can create a measure use the following DAX to get the last purchasing date per users based on current date in the row.. 

Last Purchasing =
MAXX (
    FILTER (
        ALL ( Purchasing ),
        Purchasing[Purchasing Date] < MAX ( Purchasing[Purchasing Date] )
            && Purchasing[Customer] = MAX ( Purchasing[Customer] )
    ),
    Purchasing[Purchasing Date]
)

And then create a measure to use the following DAX to get the duration.

Duration =
IF (
    ISBLANK ( [Last Purchasing] ),
    BLANK (),
    1
        * ( MAX ( Purchasing[Purchasing Date] ) - [Last Purchasing] )
)

[Anonymous]

@Anonymous,

Not quite exact what I want. 

The way you get the duration is subtracting the purchasing date with the previous date. Thus, you have missed out the last purchasing date which I want to subtract with 31May 2018.

What I want is subtracting the purchasing date with the following date. Furthermore, can I have it in query M? Because when I applied your code in DAX, i have encountered the problem of "RESOURCES EXCEEDED".

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try these calculated columns

Numbering =
RANKX (
    FILTER ( Table1, Table1[Customer] = EARLIER ( Table1[Customer] ) ),
    [Purchasing Date],
    ,
    ASC,
    DENSE
)

Duration of No Show =
VAR NextDate =
    CALCULATE (
        VALUES ( Table1[Purchasing Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Customer] ),
            Table1[Numbering]
                = EARLIER ( Table1[Numbering] ) + 1
        )
    )
VAR Next_date =
    IF ( ISBLANK ( NextDate ), DATE ( 2018, 5, 31 ), NextDate )
RETURN
    DATEDIFF ( Table1[Purchasing Date], Next_Date, DAY )

[Zubair_Muhammad]

@Anonymous

See sample file attached

[Anonymous]

@Zubair_Muhammad

This is the result that I want. But is that possible to do it in M Query? Because after this i will have to match back the numbering to see how many of them have subsequent purchasing or comeback after certain period of duration. I'm not sure how can I move forward if doing it in DAX.