Forum Discussion
Help With finding the earliest Date
If you don't want to use DAX - you can get the same result in the Query Editor using Group By
1) Duplicate your Table
2) then Group By - Customer ID and the new Column "First Contact" you are creating based on the MIN date for each Customer ID
3) Close & Apply
4) Create a Matrix - drag First Contact to the Rows and Customer ID to the Values
(change to Distinct -although the values are already distinct because we did the Group BY)
Follow the picture below...
OPTION 2
You can actually achieve the same result with a simple DAX Column in your current Table
First Contact Column = CALCULATE ( FIRSTNONBLANK('Table'[Date],1), ALLEXCEPT('Table', 'Table'[Customer ID]) )Then Create a Matrix HOWEVER
1) use the First Contact Column in the Rows (keep only Year and Month from the Hierarchy)
2) drag First Contact Column again but this time to the Values
AND this time you have to change the default earliest to distinct count
Hope this helps! :smileyhappy:
Let me know if you have any questions!
Christian,,
Thank you for the time and the reply
We are a sales organization and we contact customers repeatedly, month in and month out. If we use the distinctcount would it count the same customer next month (or 6 months from now) again the first time we contacted them in THAT month?
As for the month period, sometimes we look at quarters, years, etc. I have a timeline built in that allows us to set the parameters as desired. The month was not important and I should have omitted it. Sorry for the confusion.
HW
Helper IYes, it would - it all depends on the context, though.
Without context, the measure using DISTINCTCOUNT will count every customer in your table only once. But the true magic of DAX is that it is context-sensitive: if you provide a context the measure will be evaluated in that context.
For example, if you set up a bar chart showing customer counts per month (months on x-axis, measure on y-axis), the context is the list of available months and the measure will be evaluated per month, i.e. customers are counted separately per month.
Make sense?