Forum Discussion
First purchase analysis
Hi,
I'm looking into building a customer report and would like to know the dax formula for finding out customers first purchases and then possibly seeing what the customer then buys next?
Is there anyone that can help with this?
1 Reply
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
)
