How to Calculate the number of people who were tenants within a date range/ time period

[Morishy]

I would really appreciate some help with a DAX calculation to count the number of customers who are or were tenants with a time period. Below a small table, a sample for customers who would have occupied a property from (start date) and ended their tenancy (end date). 

So for example in 2007, I would have 5 customers who started a tenancy. For CustomerID = 1 they would not have been a tenant in the period after 2013 but would count towards the total number of customers between 2007 and 17/08/2013 (this would mean also counting the blanks for the time when they were still in tenancy)

It's easy to filter on excel on both date by filtering but being new to DAX - I think this may require a function I am not yet fimilar with.

CustID		Start Date		End Date
1		19/11/2007		18/08/2013
2		12/11/2007		18/12/2011
3		19/11/2007		30/05/2010
4		10/12/2007		30/12/2018
5		07/01/2008		02/06/2018
6		28/11/2005
7		29/09/2003
8		24/03/2004
9		05/09/2005
10		16/02/2004
11		01/11/2010		18/12/2011
12		07/08/2009		30/05/2012
13		15/07/2020		30/12/2018
14		15/06/1987		02/06/2019
15		01/01/1990
16		28/11/2005
17		29/09/2003
18		24/03/2004		02/06/2020
19		05/09/2005		14/05/2022
20		10/12/2007

Thank you for taking your time to read and assist - Please let me know if you have any questions.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your expected visualization looks like, but please check the below picture and the attached pbix file.

I suggest having a disconnected calendar table like below.

Customers count measure: =
COUNTROWS (
    FILTER (
        Customer,
        Customer[Start Date] <= MAX ( 'Calendar'[Date] )
            && OR ( Customer[End Date] >= MIN ( 'Calendar'[Date] ), BLANK () )
    )
)

[Morishy]

Thank you @Jihwan_Kim.  My Visualization will be a Line and clustered column chart as I need to overlay another table of activity. Is it nessecary to create a calender table, could I use the date within the customer table?

[Jihwan_Kim]

Hi,

I suggest having a calendar table. If the date column in the customer table is used for the slicer, then I think it will show a very different result.