Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I use this formula below and it works fine. But if i filter and see region-wise lost customers pbi recalculates for every filter which i don't want.
I need customers to be counted as lost if there's no purchase last x days at anywhere, no matter which regions filtered.
For example, customer might be purchased sth @ place X, but didn't purchased last 100 days @ place Y. This customer shouldn't be counted as lost In theory it's lost for place Y but it's still our customer. How can i differentiate formula for this purpose?
sample screenshot of a simple calculated table...
..added data relationship screenshot if needed.
LostCustomer = SUMX(VALUES('Customer Data'[NEW BOOKING CUSTOMER]);IF('Customer Data'[CustomerLostDate]>=[FirstDateSelected] && 'Customer Data'[CustomerLostDate]<=[LastDateSelected];1))
FirstDateSelected = FIRSTDATE(DATEADD('Date'[DATE];0;MONTH))
LastDateSelected = LASTDATE(DATEADD('Date'[DATE];0;MONTH))
CustomerLostDate = CALCULATE(MAX('New Customer Check Table'[DATE]);CALCULATETABLE('New Customer Check Table';FILTER(ALL('Customer Data');'Customer Data'[NEW BOOKING CUSTOMER] = MAX('Customer Data'[NEW BOOKING CUSTOMER]));FILTER(ALL('Date');'Date'[DATE]<=MAX('Date'[DATE]))))+[LostDaysLimit]
LostDaysLimit = [DaysCustomerLost]+1
Thanks,
Caglar
Hi @Anonymous,
Where did you filter the regions, and which column is it? I cannot see it in your shared screenshots.
In addition, could you share a sample pbix file with just some mock data which can reproduce the issue, so that we can better assist on it?
Regards
Hi @v-ljerr-msft,
I couldn't get same results with a mock .pbix file so i share the original one 🙂
POL refers to regions by the way,
here is the link,
.pbix
https://drive.google.com/open?id=0B_SyDMpXEmrWamFHbGRfRDZLaFU
excel raw data
https://drive.google.com/open?id=0B_SyDMpXEmrWSFR5M2E2dWJpbGs
Thx,
User | Count |
---|---|
94 | |
77 | |
71 | |
62 | |
58 |
User | Count |
---|---|
110 | |
103 | |
84 | |
65 | |
62 |