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
)
