Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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

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])

 

Capture.PNG

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.

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.