cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MagsQ Frequent Visitor
Frequent 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

Accepted Solutions
Super User
Super User

Re: Cumulative count of live Unique ID's on a date


@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
Super User

Re: Cumulative count of live Unique ID's on a date


@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

MagsQ Frequent Visitor
Frequent Visitor

Re: Cumulative count of live Unique ID's on a date

Thank you, this worked for me

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 148 members 1,630 guests
Please welcome our newest community members: