Count Distinct Values By Column and by Date

[EVEAdmin]

Hi all

I have a table similar to the following

The table is already link to a DateTable

DBName is the name of the database providing the data

CustomerID is the ID of each customer in that database. 

As there are multiple databases, the customer ID can be repeated. Therefore, if an ID is listed in 2 different databases, it will actually identify 2 different customers.

I need to calculate the number of unique customers by year.

Hope I am clear, else I can provide more details, thank you.

[RakeshNag · Accepted solution]

Hi,

Please find the below DAX measure solution.

DistinctCountOfCustomer = CALCULATE(DISTINCTCOUNT(DB_Table[CustomerID]),VALUES(Date_Table[Year]))

It is working fine. Please check and advise if it also works as per you.

Thanks,

Rakesh

[RakeshNag · Accepted solution]

Hi,

Please find the below DAX measure solution.

DistinctCountOfCustomer = CALCULATE(DISTINCTCOUNT(DB_Table[CustomerID]),VALUES(Date_Table[Year]))

It is working fine. Please check and advise if it also works as per you.

Thanks,

Rakesh

[EVEAdmin]

@RakeshNag thank you.

That returns unique customers by Year. I need unique customers by DBName by year 

[amitchandak · Accepted solution]

If you need across the used combined column. If you need by DB and By year. Then you can take distinct of customer ID. But if you are using DBname in your visual then the combined one will also work.

[amitchandak]

Not sure I got it completely.

There can be a few ways. But I will create a combined Column and then take distinct count

New column = Table[DBName]& "-"&Table[CustomerID]

Count Customer = calculate(distinctcount(Table[New column]))

[EVEAdmin]

@amitchandak that is simple and should work, thank you.

HOwever, how do I count unique customers by Year?

[amitchandak]

You can use year filter or datesytd or totalytd. make sure you have date table

example.

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/