Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |