How to get lost customers over a date range?

---

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