Forum Discussion
count distinct
Good morning,
I'm trying to calculate the number of customer's that bought something this and the last year.
I was able to calculate the customer's of this year like this..
Carlaf , In place to datesmtd, use datesytd
you need retain
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY
3 Replies
- amitchandakSuper User
Carlaf , In place to datesmtd, use datesytd
you need retain
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY
- Stemar_AubertResolver I
Another method would be to use the time column of your data as a slicer, select for the current year.
Meanwhile, place your customers in the visual (table for exemple) and select count (distinct) in the summarization pannel :
- AnonymousNot applicable
Hi Carlaf
It's not clear where you want to place these two measures for comparison.
Suppose we have a table like this.
1. If placed in a same visual for comparison, you can use [Year] as the axis and put the measure [customer's_today] in the value field.
2. If you want to compare this year's value with last year's value and compare the measures in two different visualizations, please try.
customer's_last_year1 = CALCULATE ( DISTINCTCOUNT ( CUSTOMERS_DETAILS[CUSTOMER] ), SAMEPERIODLASTYEAR ( DATESYTD ( STAT_VEN[TMPDDATA] ) ), FILTER ( STAT_VEN, STAT_VEN[sales_this_year] > 0 ) )or
customer's_last_year2 = CALCULATE ( DISTINCTCOUNT ( CUSTOMERS_DETAILS[CUSTOMER] ), PARALLELPERIOD('STAT_VEN'[TMPDDATA],-1,YEAR), FILTER ( STAT_VEN, STAT_VEN[sales_this_year] > 0 ) )3. Comparison of the same period.
customer's_last_year3 = VAR _last_date = LASTDATE ( 'CUSTOMERS_DETAILS'[DateCloumn] ) VAR _result = CALCULATE ( DISTINCTCOUNT ( CUSTOMERS_DETAILS[CUSTOMER] ), SAMEPERIODLASTYEAR ( DATESYTD ( _last_date ) ), FILTER ( STAT_VEN, STAT_VEN[sales_this_year] > 0 ) ) RETURN _resultBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data