Forum Discussion
Distinct counts active clients per date
Hello,
I have a question about using distinct count based on a number and date in Power Bi.
I have 1 column with accountnumers which have duplicates, column accountnum, I also have dates on which
transactions have been made.
I have to create a bar chart showing active customers per year and month based on accountnumber and transaction date.
When i use distinct count I only get a total of the amount of unique accountnumber, is there any way to use distinct count combined with the date to show active clients per date?
Example:
Accountnum: Date
551 01-10-2016
551 01-11-2016
551 01-12-2016
551 01-01-2017
551 01-02-2017
551 01-03-2017
552 01-01-2015
552 01-03-2015
553 01-05-2015
554 01-01-2016
556 01-04-2017
551 should show as active in the period 10-2016 - 03-2017 in a bar chart filterable on year and month.
Hope someone can help out!
1 Reply
- AnonymousNot applicable
HI SanderAR,
You can use summarize function to get the summary table:
Table = SUMMARIZE(Test,[Accountnum],[Date].[Year],[Date].[MonthNo],"Distinct Count",DISTINCTCOUNT(Test[Accountnum]))
Regards,
Xiaoxin Sheng