Forum Discussion
Helper IICalculate Active Customers
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;
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 _CountLet me know if you have any questions.
3 Replies
- Greg_Deckler
Community Champion
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/409365Otherwise, 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. 
sglendenningSample 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
Super User
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 _CountLet me know if you have any questions.
