Forum Discussion

hoggwildd's avatar
hoggwildd
Regular Visitor
9 years ago
Solved

Help With finding the earliest Date

I have a database that I need to be able to count only the first time a row of data appears. Below is part of the database:   Date Customer ID Apt set Confirmed issued sat Result Sale Amo...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    hoggwildd

    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!

     

Sean's avatar
Sean
Community Champion
9 years ago

hoggwildd

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!

 

HPC's avatar
HPC
Regular Visitor
2 years ago

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