Forum Discussion
How to get lost customers over a date range?
Hi guys, I am currently trying out Pbi and evaluating it for my company. I have a test case that I cannot seem to work out. I am not fluent in DAX or Pbi.
This is a snapshot of my data. What I would like to do is:
| canum | gb_contracttype | gb_salesmancode | billcycle | startdate | enddate | customer | gb_veh_subgroup | gb_subgroupcode |
| LCVL156123 | salesman a | MONTHLY | 15/8/2016 | 14/8/2018 | C00013958 | 1.3 | ||
| LCVL789465 | salesman b | MONTHLY | 1/4/2014 | 28/2/2017 | C00008742 | BUS | TVD | |
| LCV456123 | salesman a | MONTHLY | 26/3/2014 | 25/3/2017 | C00003097 | BUS | TVD |
Show on a dashboard, the number of active contracts in a day in the form of a line chart. For e.g, 14/8/16 - 2000 active contracts, 15/8/16 - 2001 active contracts.
I then want to be able to drill down and find that one contract we gained, who is the salesman and what is the subgroup of the vehicle sold.
-End
So far I have not been able to find examples on lost customers by evaluating their status (active or non-active) over a date range.
Any help would be greatly appreciated. Thank you.
4 Replies
- BhaveshPatel
Super User
THERE ARE COUPLE OF QUESTIONS YOU SHOULD MAKE IT CLEAR BEFORE ANSWERING YOUR QUESTION
1.How do you define the particular contract is active or inactive. Is there any criteria?
2. Is there any business logic that identifies the lost customers?
3. it would be better to share dimensions table in your data model as well which will make your question more clear.
- Sorry for missing these points out.
1. Ideally my X-axis has a range of dates, could be any 7 days i input in my filter. For each day in my x-axis, if my [start date] <= the date, and [end date] > the date then it's considered active. Anything outside of this range is considered inactive.
2.Lost customers are those contracts that were present during the start of the date range and by the end of the specified date range is no longer present.
3. i don't understand what you mean by dimensions table.
Thanks in advance.
- Baskar
Resident Rockstar
Cool dude,
Look this link , am sure it will help u
http://www.daxpatterns.com/new-and-returning-customers/
let me know if not
Show on a dashboard, the number of active contracts in a day in the form of a line chart. For e.g, 14/8/16 - 2000 active contracts, 15/8/16 - 2001 active contracts.
I then want to be able to drill down and find that one contract we gained, who is the salesman and what is the subgroup of the vehicle sold.
Hi jialiang，
Add a calendar table in your scenario, then create the following measure, then create a line chart using date column in calendar table and the measure .
Active contracts =
COUNTROWS(
FILTER( Table1, Table1[startdate]<= MIN('Date'[DateKey]) && Table1[enddate] >= MAX('Date'[DateKey]) )
)In addition, it is not possible to directly dill down from the line chart to get details of the active contracts, but you can create a table visual as follows, and drag the measure to visual level filter of the table and set the value to be greater than 0. Moreover, you can create a date slicer to filter table visual and line chart. For more deatils, please review this attached PBIX file.
Thanks,
Lydia Zhang
