How to get lost customers over a date range?

[jialiang]

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.

[Anonymous]

---

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

[Baskar]

Cool dude,

Look this link , am sure it will help u 

http://www.daxpatterns.com/new-and-returning-customers/

let me know if not 

[BhaveshPatel]

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.

[jialiang]

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.