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!
Yes, 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?
Christian,
I have been working with this and cannot make it work. Using the data with a distinctcount measure I get:
| Apt set | distinct count |
| 1 | 14 |
| Grand Total | 14 |
That is using a timeline slicing only to February, and that is accurate. But when I make the timeline March I get:
| Apt set | distinct count |
| 1 | 9 |
| Grand Total | 9 |
that should only be 2.
Customer ID 397508 & 397509 are the only two customers that FIRST contacted us in March according to the data. This is why I thought I needed to write a measure that would look up the first date for each customer and then count only that row of data.
The actual database has a lot more columns, one of which would be "source" and that would really be how we want to set up the table but I dont think that will change this problem. I am telling you in case you think it would, if so I can add that to the sample to give you a better idea.
Again I cannot thank you enough for your time and assistance. This is kicking my ass!
HW
Ah, I see - I misunderstood your requirement: I thought you were looking for unique customers per month (or any other timeperiod) while you are actually looking for unique NEW customers per month (where "new" means first contact).
In that case, your easiest option is to use a Summarize table, i.e. click on "New Table" in the Modelling tab and create a new table using this kind of DAX statement:
Customer first contact = SUMMARIZE(Table, CustomerID, "FirstDate", MIN(Date))
This will give you a table where each customer is paired with the first date of contact. If you know SQL, the Summarize function is the DAX equivalent of GROUP BY. This table can then be sliced as described earlier.
Now, there is another way to do this which is a bit more complicated, but also more powerful: if you want to reason over both, the first date as well as data from later contacts (for example, in order to figure out the average time elapsed between a first contact and a follow-up contact) then you would want to put the first date right into the table that holds all the contacts. You can do that by using the EARLIER function - seriously powerful DAX magic! :-)
Hope this helps!
Christian