Forum Discussion

GavRay's avatar
GavRay
Regular Visitor
8 years ago

Distinct count active customer by month

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 da...
v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

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's avatar
    GavRay
    Regular Visitor
    8 years ago

    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