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
Icon for Community Support rankCommunity Support
8 years ago

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