Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
2. Calculate the earliest activity date on or before which the user is considered churned
3. If the Latest User Activity Date <= Date Lost Contact Threshold then 1 else 0
4. SUM the 1s & 0s to determine the total population of "lost contacts"
???None of these work, they assess to BLANK() or 0:
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.
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
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
79 | |
68 | |
60 |