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!
If you simply want the number of unique customers per calendar month, you can have a measure like this:
unique customers = DISTINCTCOUNT(CustomerID)
And then you use the month name as context in your visualizations - that way, you will see unqiue customers for January, February etc. For convenience, I would add the month name as calculated column ot your table using the DAX function MONTH.
Typically, however, instead of looking at calendar months, monthly metrics are based on a rolling window of 28 days - something like this:
unique customers moving 28 days =
CALCULATE(
[unique customers],
DATESINPERIOD(
'Date'[Date],
LASTDATE( 'Date'[Date]),
-28, DAY
)
)
This way you are normalizing for different month lengths and you also get a valid and complete monthly metric every day.
Hope this helps!
Christian