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!
Sorry to hear that - don't give up, DAX is an awesome tool once you got the hang of it!
I copied your data into a pbix and created what I think you need. Unfortunately, I don't know how I can share that file with you ... Maybe the following screenshot will be enough? It shows the statement you need to create a customers table that holds dates for the first contact for each customer and all the data that is in that summarized table. The bar chart shows counts of customers per month - note how it only shows 2 for March.
The DAX for the Month column looks like this:
Month = MONTH(Customers[First contact])
Hope this helps!
Cheers,
Christian
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!
This is really amazing. Thanks for sharing. Is there a way to find difference between last and first data point based on created on date?
bomb.com
Really Nice. Thanks.
Hi JacksonAndrew, Would you please explain why "1" is used as the expression in "Firstnonblank" function? Thank You.
Option 2 helped me get unstuck where I was trying to find the original date of a listing of records by for a particular field. Thanks!
Hi hansen922, Would you please explain why "1" is used as the expression in "Firstnonblank" function? Thank You.
Dear Sean, my reply might come to as a surprise after 7 years from your post. I'm new to Power BI. I understood and managed to get the "First Date" thru generating the 2nd table in Power Query. I could not understand the logic in your DAX formula. Would you kindly explain the logic? Thank You
