cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Cumulative count of live Unique ID's on a date

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 DtEnd DateDESCRIPTION
298802/11/200724/10/2008Expired
298805/12/200818/12/2009Expired
567421/03/201620/06/2017Expired
586809/05/201629/06/2016Expired
586809/05/201608/05/2017Expired
586803/06/201602/06/2017Expired
586804/06/201603/06/2017Expired
586818/05/201717/05/2018Expired
586810/06/201809/06/2019Expired
1067816/11/200707/11/2008Expired
1067814/11/200820/11/2009Expired
1067827/11/200919/11/2010Expired
1067826/11/201015/11/2013Expired
1067821/11/201320/11/2015Expired
1067821/11/201520/11/2016Expired
1067821/11/201620/11/2017Expired
1067808/09/201721/12/2018Expired
1574812/10/200703/10/2008Expired
1574810/10/200816/10/2009Expired
1574820/11/200905/11/2010Expired
1574812/11/201004/11/2011Expired
1574811/11/201102/11/2012Expired
1789215/06/201814/06/2019Expired
1882809/03/200711/04/2008Expired
1882818/04/200810/04/2009Expired
1882824/04/200916/04/2010Expired
1993619/01/200711/01/2008Expired
2171022/08/200811/09/2009Expired
2171018/09/200910/09/2010Expired
2171001/10/201023/09/2011Expired
2171030/09/201121/09/2012Expired
2171028/09/201220/09/2013Expired
2243421/09/200712/09/2008Expired
2243419/09/200825/09/2009Expired
2243402/10/200924/09/2010Expired
2243401/10/201023/09/2011Expired
2243421/10/201112/10/2012Expired

 

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
 
1 ACCEPTED SOLUTION
Super User II
Super User II


@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)
)

 

View solution in original post

2 REPLIES 2
Super User II
Super User II


@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)
)

 

View solution in original post

Thank you, this worked for me

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors