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

Anonymous

 

See sample file attached