First purchase analysis

Hello:

You can try the following: Assume Sales Fact Table is named 'Sales Table' and a column in their is name [purchase date]

Date Of First Purchase = FIRSTDATE('Sales Fact Table'[purchase_date])

OR

First Purchase Date = MIN(‘Sales Table’[purchase date])

You can use RANKX to rank your purchase dates and select the second one.

Total Sales = SUM('Sales Table'[Sales Amount])

RankX = RANKX(ALL('Sales Table'[purchase date]), [Total Sales],,DESC))

If needed:

Days between First purchase and Second purchase =

VAR temp =

    ADDCOLUMNS (

        VALUES ( 'Sales Table'[purchase_Date] ),

        "RANK", RANKX (

            VALUES ( 'Sales Table'[purchase_Date] ),

            [purchase_Date],

            ,

            DESC,

            DENSE

        )

    )

RETURN

    DATEDIFF (

        MINX ( FILTER ( temp, [RANK] = 2 ), [purchase_Date] ),

        MINX ( FILTER ( temp, [RANK] = 1 ), [purchase_Date] ),

        DAY

    )