Forum Discussion
Distinct count based on value and 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!
Basically it should work.
If you want to calculate the active users per date, you can just create a measure to distinct count accounts group on date level.
Active Users = CALCULATE ( DISTINCTCOUNT ( Table[AccountNum] ), ALLEXCEPT ( Table, Table[Date] ) )Regards,
4 Replies
Hey,
does this not work:
- Use the date on the x-axis (make sure that the property "Type" of the x-axis is set to categorical)
- Use the column accountnum in the value section of the visual and set the aggregation to Count (Distinct)
Hope this helps
Hi Tom,
Thanks for the fast reply and solution, it seems to work but I still only get the total amount of 5xxx and year and month
filters don't work, should they? If so it's a relational problem, which I can solve myself.
Thanks for the help in advance!
If you want to calculate the active users per date, you can just create a measure to distinct count accounts group on date level.
Active Users = CALCULATE ( DISTINCTCOUNT ( Table[AccountNum] ), ALLEXCEPT ( Table, Table[Date] ) )Regards,
