Forum Discussion
Customer count over time
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.
Hi Anonymous ,
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
4 Replies
Anonymous can you share sample data? How the date is assigned to the customer?
Anonymous 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
Hi Anonymous ,
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
