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

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.