Forum Discussion

SanderAR's avatar
SanderAR
New Member
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not 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