Find the No. of Customers that were there in the Last Year data as well as

[amirabedhiafi]

I have a small data which has two tables in it. Sales table contains the two columns, Order Date, Customer Name, and Date Table contains the Date column with two years of data (2015 and 2016). 

I am being required to find the No. of Customers that were there in the Last Year data as well as in the Current Year data using DAX.

@amitchandak 

[Pragati11]

Hi @amirabedhiafi ,

You can do the following:

1. Create a relationship between SALES and DATE table on OrderDate (Sales) and Date(Date) columns.
2. Create a bar chart.
3. Move YEAR part of Date(DATE) column in the x-axis and COUNT(CustomerName) in the y-axis.

This will give you number of customers by year - for both 2015 and 2016.

Thanks,

Pragati

[amirabedhiafi]

What about using DAX?

[Pragati11]

Hi @amirabedhiafi ,

You can use following DAX:

ThisYearCount = CALCULATE(COUNT(Sales[CustomerName]), DATESYTD(Date[Date]))

LastYearCount = CALCULATE([ThisYearCount], SAMEPERIODLASTYEAR(Date[Date]))

Check if these work.

Thanks,

Pragati

[benhoward00 · Accepted solution]

I read this as the number of customers, not the number of sales, therefore distinct count would be better.  

No of customers thisyear =
CALCULATE(DISTINCTCOUNT(sales[customer name]), 'Date'[Year] = YEAR(today()))

No of customers lastyear = CALCULATE(DISTINCTCOUNT(sales[customer name]), SAMEPERIODLASTYEAR'Date'[Date]')
 

[amirabedhiafi]

These are the choices, I am confused which one is correct :