Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Subtract current row from subsequent row

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.

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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 )

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        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 )