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

        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.