distinct count of account, based on Service start/end date

[PowerBIPilgrim]

 Hi All

I have 2 tables: My Date Dimension table and my Service Table. Date is joined to both PurchaseDate and CancelledDate, with PurchaseDate being the active join.

My Service Table looks like this:

CustomerID	ServiceID	PurchaseDate	CancelledDate
1	20	23/07/2015	NULL
1	22	05/02/2019	30/07/2020
2	81	10/10/2009	14/05/2019
3	30	29/11/2012	NULL
3	1005	16/05/2016	22/09/2020

I currently have the following Measure for calculating the Total Active Services over time:

Current Services = 
CALCULATE(COUNTx(FILTER(Service,Service[PurchaseDate]<=max(BI_Dim_Calendar[Date]) 
&& (ISBLANK(Service[CancelledDate]) 
|| Service[CancelledDate]>max(BI_Dim_Calendar[Date])
)
),
(Service[ServiceID])),CROSSFILTER(Service[PurchaseDate],BI_Dim_Calendar[Date],None))

Which works lovely, however I am trying to get a distinct count of Customers over time, I'm using the same filter but when I do this:

Current Customer = 
CALCULATE(DISTINCTCOUNT(Service[AccountID]),       
        FILTER(Service,Service[PurchaseDate]<=max(BI_Dim_Calendar[Date]) 
        && (ISBLANK(Service[CancelledDate]) 
        || Service[CancelledDate]>max(BI_Dim_Calendar[Date])
            )
        ),
CROSSFILTER(Service[PurchaseDate],BI_Dim_Calendar[Date],None))

I get a number that is definitively wrong (I know the approximate number for the current month to be 90K) whereas that expression gives me a total of around 130 - so clearly I've missed something, most likely order of operations.

If someone can enlighten me as to what I've done wrong, I would massively appreciate it.

[amitchandak]

@PowerBIPilgrim , This formula seems correct. I did not see the account ID in your sample .

Do you need Customer ID or service ID ?

Current Customer =
CALCULATE(DISTINCTCOUNT(Service[Customer ID]),
FILTER(Service,Service[PurchaseDate]<=max(BI_Dim_Calendar[Date])
&& (ISBLANK(Service[CancelledDate])
|| Service[CancelledDate]>max(BI_Dim_Calendar[Date])
)
),
CROSSFILTER(Service[PurchaseDate],BI_Dim_Calendar[Date],None))

[PowerBIPilgrim]

My Apologies - Typo on my part - AccountID should read CustomerID - so for clarity:

Current Customer =
CALCULATE(DISTINCTCOUNT(Service[Customer ID]),
FILTER(Service,Service[PurchaseDate]<=max(BI_Dim_Calendar[Date])
&& (ISBLANK(Service[CancelledDate])
|| Service[CancelledDate]>max(BI_Dim_Calendar[Date])
)
),
CROSSFILTER(Service[PurchaseDate],BI_Dim_Calendar[Date],None))

This does not produce the correct output, I have a value of approximately 90k for active customers, yet that measure gives me a value of 130 for the current month

[amitchandak]

@PowerBIPilgrim , the formula seems correct.  Can you share sample pbix after removing sensitive data.

[PowerBIPilgrim]

Thinking about this, I think I've worked out the why it's happening - it's my order of operations:

I'm doing a distinctcount on my accountID, then that result is being filtered by the purchaseDate and CancelledDate as opposed to:

Filtering the table by purchaseddate and cancelleddate and then doing a distinctcount on the accountID from that list.

however - I'm not sure how to fix this.

[PowerBIPilgrim · Accepted solution]

I finally managed to solve this myself (after many a 4-letter word was uttered):

Current Customer = 
CALCULATE(COUNTROWS(
    DISTINCT(
SELECTCOLUMNS(
FILTER(Service,Service[PurchaseDate]<max(BI_Dim_Calendar[Date]) 
&& (ISBLANK(Service[CancelledDate]) 
|| Service[CancelledDate]>max(BI_Dim_Calendar[Date])
)
), "AccountID", Service[AccountID]
)
)
),CROSSFILTER(Service[PurchaseDate],BI_Dim_Calendar[Date],None))

[PowerBIPilgrim]

**PIBX Removed**

Just a note, in stripping out some of the potentially sensitive information, I think I may have introduced an error into the dataset, however I've tested on the live dataset, and it's definitely giving me the wrong count.

See the below, from my live dataset with a couple of field names blanked out, the value on the left is a very approximate count of active customers using a couple of date filters and a distinct count, it gives a value of 101K, vs the 144 from my measure

Thanks