Forum Discussion
Customers Retention by quarters
manar_alamri
try this measure:
Customer Retention =
VAR CurrentPeriodStart = STARTOFQUARTER(Table1[date])
VAR CurrentPeriodEnd = ENDOFQUARTER(Table1[date])
VAR PreviousPeriodStart = PREVIOUSQUARTER(STARTOFQUARTER(Table1[date]))
VAR PreviousPeriodEnd = PREVIOUSQUARTER(ENDOFQUARTER(Table1[date]))
RETURN
CALCULATE(
DISTINCTCOUNT(Table1[guest code]),
FILTER(
Table1,
Table1[date] >= CurrentPeriodStart && Table1[date] <= CurrentPeriodEnd &&
Table1[guest code] IN
CALCULATETABLE(
VALUES(Table1[guest code]),
Table1[date] >= PreviousPeriodStart && Table1[date] <= PreviousPeriodEnd,
Table1[Center] = EARLIER(Table1[Center])
)
)
)
You can adjust the period if you need monthly retention instead of quarterly by modifying the STARTOFQUARTER and ENDOFQUARTER functions to STARTOFMONTH and ENDOFMONTH respectively.
Regards
- manar_alamri1 year ago
Helper I