Calculate rolling n-day churn based on user-selected date slicer

[Anonymous]

I have a simple data model:

1. Fact table

2. Dates table (ideally would like to avoid this)

I need to calculate if a device has not had activity for at least 7 days preceding the ending date of a "between"-style date range slicer.  For example, as of 2/28/21, how many devices' Latest Activity Date was on or before 2/20/21?  The 2/20/21 date is relative to the user date slicer END date (i.e. MAX(Dates[Date]) )

The logical steps would be as such:

1. Calculate the "Latest Activity Date" for each device

Latest Date by User (Measure) = CALCULATE(MAX(Fact[Latest Date by User (Column)]),ALLEXCEPT(Fact,Fact[User ID],Fact[Platform]))

2. Calculate the earliest activity date on or before which the user is considered churned

Date Lost Contact Threshold = MAX(Dates[Date]) - 7

3. If the Latest User Activity Date <= Date Lost Contact Threshold then 1 else 0

Device Lost Contact = IF([Latest Date by User (Measure)]<[Date Lost Contact Threshold],1,0)

4. SUM the 1s & 0s to determine the total population of "lost contacts"

???None of these work, they assess to BLANK() or 0:

Lost Contacts = CALCULATE(DISTINCTCOUNT(Fact[User ID]),FILTER(Fact,[Device Lost Contact]=1))

Lost Contacts = SUMX(Fact,Fact[Device Lost Contact])

My challenge:

1. I need a computed column for the Latest Activity Date since it's agnostic of the date range slicer (needs to be calculated before we filter the context)--I could alternatively use a FILTER(ALL()) combo

2. I seem to be fighting against myself in terms of row context, where I need to use DAX to leverage the date slicer value, but I need to iterate across every user to calculate the "churn" status

I know this is all somewhat vague, but this is highly sensitive data, so it's difficult to post too much.

[Anonymous]

HI @Anonymous,

If you want to create a measuring format based on filter selections, it is possible. You can create a what-if parameter table and use select value to extract the selections and use them in your formula.

If you mean to create a calculated column that dynamically interacts with filters, it is impossible.
Current power bi does not support create a dynamic calculated column/table based on filter selections, they are work on different data levels.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous ,  Try measures like

Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-7,Day))

Rolling 7 day before 7= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date])-7,-7,Day))

Not active in last 7 but was active before = if(isblank([Rolling 7 day]) && not(isblank([Rolling 7 day before 7])),1,blank())

also refer these blogs on same line

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458