Cumulative count of live Unique ID's on a date

[MagsQ]

HI I am struggling to get a cumulative total of live Unique id's on a given date.

My ID's field is duplicated as someone can have more than 1 start and end date.

I was wondering if there is a way of getting a unique count of id's with a live subs on a said date eg: 16/11/2018

My table looks like this

Unique id (May contain Dupes)	Start Dt	End Date	DESCRIPTION
2988	02/11/2007	24/10/2008	Expired
2988	05/12/2008	18/12/2009	Expired
5674	21/03/2016	20/06/2017	Expired
5868	09/05/2016	29/06/2016	Expired
5868	09/05/2016	08/05/2017	Expired
5868	03/06/2016	02/06/2017	Expired
5868	04/06/2016	03/06/2017	Expired
5868	18/05/2017	17/05/2018	Expired
5868	10/06/2018	09/06/2019	Expired
10678	16/11/2007	07/11/2008	Expired
10678	14/11/2008	20/11/2009	Expired
10678	27/11/2009	19/11/2010	Expired
10678	26/11/2010	15/11/2013	Expired
10678	21/11/2013	20/11/2015	Expired
10678	21/11/2015	20/11/2016	Expired
10678	21/11/2016	20/11/2017	Expired
10678	08/09/2017	21/12/2018	Expired
15748	12/10/2007	03/10/2008	Expired
15748	10/10/2008	16/10/2009	Expired
15748	20/11/2009	05/11/2010	Expired
15748	12/11/2010	04/11/2011	Expired
15748	11/11/2011	02/11/2012	Expired
17892	15/06/2018	14/06/2019	Expired
18828	09/03/2007	11/04/2008	Expired
18828	18/04/2008	10/04/2009	Expired
18828	24/04/2009	16/04/2010	Expired
19936	19/01/2007	11/01/2008	Expired
21710	22/08/2008	11/09/2009	Expired
21710	18/09/2009	10/09/2010	Expired
21710	01/10/2010	23/09/2011	Expired
21710	30/09/2011	21/09/2012	Expired
21710	28/09/2012	20/09/2013	Expired
22434	21/09/2007	12/09/2008	Expired
22434	19/09/2008	25/09/2009	Expired
22434	02/10/2009	24/09/2010	Expired
22434	01/10/2010	23/09/2011	Expired
22434	21/10/2011	12/10/2012	Expired

I tried to get a work around using some help from one of the post by @v-cherch-msft  on Tracking Cumulative Subscriptions over Time  by

creating a table 

Calendar Table = CALENDAR(TODAY()-3650,TODAY()+365)

and then a measure

Measure = CALCULATE(DISTINCTCOUNT('Paid Subs Count'[FX_PARTY_UID]),

FILTER(GENERATE('Calendar Table','Paid Subs Count'),

'Paid Subs Count'[START_DATE]<= 'Calendar Table'[Date]

&& DATE ( YEAR ( 'Paid Subs Count'[END_DATE] ), MONTH ( 'Paid Subs Count'[END_DATE]),1)>'Calendar Table'[Date]))

However it wouldnt give me a cumulative count of all Live IDs on a said date.

Any help would be greatly appreciated.

Thanks in advance

[d_gosbell · Accepted solution]

---

@MagsQ wrote:

I was wondering if there is a way of getting a unique count of id's with a live subs on a said date eg: 16/11/2018

---

I think the following should do this. Is the answer you are expecting from your sample data a value of 3? (this assumes there is no relationship between the 'Paid Subs Count' table and the Calendar table)

Active Subs = 
VAR _maxDate = max('Calendar'[Date])
RETURN CALCULATE( 
    DISTINCTCOUNT('Paid Subs Count'[Unique id]), 
    FILTER(VALUES('Paid Subs Count'[Start Dt]), 'Paid Subs Count'[Start Dt] < _maxDate), 
    FILTER(VALUES('Paid Subs Count'[End Date]), 'Paid Subs Count'[End Date]>= _maxDate)
)

[MagsQ]

Thank you, this worked for me