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

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.