Calculate Active Customers

[sglendenning]

I have the effective date, expiration date, and void date and I have a date table but it is joined with a different field other than the effective date.

When using a matrix visual by year everything looks reasonable until I get to 2024 where 2024 is low.  If I expand the matrix to the month level within a year prior to 2024 I get a much lower number.  The total for the year column prior to 2024 is ok but the month level is not correct.

I'm using;

CALCULATE(COUNT(CustomerKey),FILTER(EffectDate<=MAX('Date'[Date])&&ExpireDate>=MAX('Date'[Date])||VoidDate>MAX('Date'[Date])))

[sglendenning]

Sample Data;

AcqDate	ExpireDate	CancelDate	CustKey
4/25/2022	4/24/2023	1/16/2023	12174780
4/27/2022	4/26/2023	8/30/2022	24971035
4/25/2022	4/24/2023		16721256
4/7/2022	4/6/2023		36296808

Expected Output via Matrix;

Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23		2022	2023
4	4	4	4	3	3	3	3	3	2	2	2	2		3	2

[gmsamborn · Accepted solution]

Hi @sglendenning 

Would a measure like this help?

Active = 
VAR _Start = MIN( 'Date'[Date] )
VAR _End = MAX( 'Date'[Date] )
VAR _Count =
    CALCULATE(
        DISTINCTCOUNT( 'Table'[CustKey] ),
        'Table'[AcqDate] <= _End
            && COALESCE( 'Table'[CancelDate], 'Table'[ExpireDate] ) >= _Start
    )
RETURN
    _Count

Let me know if you have any questions.

Active Customers.pbix

[Greg_Deckler]

@sglendenning Sample data would help to be more specific. 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.