Forum Discussion
Lost Customers
Hello all,
I'm trying to find the number of customers who are 'lost' for any given month!
Lost = The number of customers who have not completed an appointment in 24 months but who have purchased before.
I have 3 tables: Dates, Appointments, and a Has Purchased Before table. Appointments and Has Purchased are linked by Customer ID. Appointments has a column called 'Status' that needs to read 'Completed'. Has Purchased Before table is just a list of folks who have bought from us.
I tried repurposing a solution I found here on the forums but it isn't looking at Appointments[Status] = "Completed" or if the Patient pops up in the Has Purchased Before table.
So far the repurposed measure looks like this:
Any help would be greatly appreciated!
3 Replies
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
jcawley , something like this with date table
lost Customer =countx(filter(summarize(Customer, Customer[Name], "_1" ,CALCULATE(sum(Sales[Sales Amount]),all(sales)),"_2",
CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-24,MONTH))
), not(isblank([_1])) && isblank([_2])),[Name])To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
