Customer count over time

[arslan7861]

Hello everyone,

I am in need of some help and haven't been able to figure this out. I have a list of customers from Dec 2022, Jan 2023 and Feb 2023, what I need is to get a distinct count of these customer month over month, if their revenue is >$0. 

For example, Dec has 2000 customers but 300 have a revenue of $0, so I need the distinct count to come back with 1700 customers. Jan has 2300 customers but 200 have a revenue of $0, so I need the distinct count to come back with 2100, etc.

Any help would be greatly appreciated. 

[v-yangliu-msft · Accepted solution]

Hi  @arslan7861 ,

You can also try the following dax:

Measure:

Measure =
CALCULATE(
    DISTINCTCOUNT('Table'[Revenue Date]),
    FILTER(ALL('Table'),
    'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Revenue]>0),'Table'[Revenue Date])

Calculated column:

Column =
CALCULATE(
    DISTINCTCOUNT('Table'[Revenue Date]),
    FILTER(ALL('Table'),
    'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Revenue]>0),'Table'[Revenue Date])

Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[parry2k]

@arslan7861 logically you should have date dimension in the model to work with dates, in the meantime you can use the following measure:

Distinct Customer Count with Revenue = 
CALCUALTE ( 
    DISTINCTCOUNT ( Table[Customer] ),
    Table[Revenue] > 0 
)

👉 Learn Power BI Subscribe to our YT channel - @PowerBIHowTo

[parry2k]

@arslan7861 can you share sample data? How the date is assigned to the customer?

[arslan7861]