Distinct count active customer by month

[GavRay]

Hi everyone

I've been looking around the community for something that might provide an answer to my problem. As with most, I am new to Power BI, specifically DAX, and am trying to build my own data model that tracks my client's data each month for me to report on and share with them.

I am at the point now where I need to do (what seems should be) a simple calculation that produces ccount of active clients per month. Something along the lines of Active clients = total clients - cancelled clients

My Customer table has customer ID, start date, cancelled date and I have the relationship with the Calendar table.

what I am struggling with is the correct DAX calculation that works out the cancelled cliets per month

Can anyone assist, or point me to where the answer already lies within the forum, most answers are around sales, but I am only looking for active customer number comparisons.

[v-juanli-msft]

Hi @GavRay

According to your explanation, it seems my previous answer has something missing of the active customer.

As you said, active customers are who have null value in the [cancelled date].

Create calculated columns

month = MONTH([start date])

year = YEAR([start date])

month/year = CONCATENATE(CONCATENATE([month],"/"),[year])

active customer =
CALCULATE (
    DISTINCTCOUNT ( Sheet4[customer name] ),
    FILTER (
        ALLEXCEPT ( Sheet4, Sheet4[group], Sheet4[month/year] ),
        ISBLANK ( [cancell date] )
    )
)

Best Regards

Maggie

[v-juanli-msft]

Hi @GavRay

Look at my test.

Since it is not clear which client should be considerd to be cancelled, I assume the condition is:

if a client's start month is before or equal to the selected month, and the cancelled month is after the selected month, this  client is considerd as "active" client, otherwise, he is an "inactive" client(cancelled client this month).

selected month = SELECTEDVALUE('calendar table'[month])

if cancelled = IF(MONTH(MAX([cancelled date]))>[selected month]&&MONTH(MAX([start date]))<=[selected month],"active","inactive")

count of cancelled = CALCULATE(COUNT(Table1[customer id]),FILTER(ALL(Table1),[if cancelled]="inactive"))

Before, please manage relationships between two tables like below.

Best Regards

Maggie

[GavRay]

Hi Maggie

Thanks so much for this!

I think my challenge may be a bit more challenging than I conveyed. So I report on Groups, each containing a number of customers (over 500 in total). My aim is to show graphically their total number of Active customers by group per month. Customers may not necessarily have a cancellation date yet so that value will be blank for active customers.

My customer table has columns as follows: Customer Id, Customer name, Group, Start date, Cancellation date, Start date month Number (calculated column), Cancellation date Number (calculated column)

My calendar table is standard

I have tried the following to try have a dynamic calculation of number of active customers per month in order to have a value to use in a graph over time;

Measure 1. Selected Month = SELECTEDVALUE(DimCalendar[Month number])

Measure 2. Start Date Active Count = IF(MONTH(MAX(CustomerTable[Start Date]))>[Selected Month];"Inactive";"Active")

Measure 3. Cancelled Date Active Count = IF(MONTH(MAX(CustomerTable[Cancellation Date]))<[Selected Month];"Inactive";"Active")

with the above

Measure 4. Active Dealer Count = CALCULATE(COUNT(CustomerTable[CustomerID]);FILTER(ALL(CustomerTable);[Start Date Active Count]="Active"&&[Cancelled Date Active Count]="Active"))

My table relationship is between CustomerTable[StartDate] & DimCalendar[Date]

I am still not getting it right unfortuantely