Dynamic time period to calculate user retention

[dungluu302]

Hi everyone,

I want to use dynamic time period to categorize user retention, with table period_time, with the value of the period_date column is 1, 7, 14, 30, 45, 60, 90 respectively.
I wrote the dax function as follows:

Customer_Classification = 
VAR TimeDifference = DATEDIFF(order_detail[Last_purchase_date], order_detail[created_at_local], DAY)

RETURN
SWITCH(
    TRUE(),
    ISBLANK(order_detail[Last_purchase_date]), "New",
    TimeDifference <= SELECTEDVALUE(period_time[period_date]), "Retained",
    TimeDifference > SELECTEDVALUE(period_time[period_date]), "Resurrected",
    "Unknown"
)

But it doesn't work, the result is time period = 1, even though I have chosen a different time period value.
Please advise me how to make it work and how to create table relationship (if any). Thanks all

[parry2k]

@dungluu302 this has the answer to your question. Dynamic segmentation – DAX Patterns

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

[dungluu302]

Hi @parry2k , Thanks for your suggestion, but with my newbie level, I still can't clear how to apply your example to my case. If you don't mind, can you guide me?