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!

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi hoggwildd,

 

Based on your description, you want to get the monthly distinct customer count, right?

If this is a case, you can refer to below steps to achieve your requirement:

1. Add calculate column "year&month" to store the value which used to group.

Year&Month = [Date].[Year]*100+[Date].[MonthNo]

 

2. Create a calculated table to show the grouped records.

 

Summary Table = SUMMARIZE(Sheet3,Sheet3[Year&Month],
    "Distinct Count",DISTINCTCOUNT(Sheet3[Customer ID]),
    "Count",COUNT(Sheet3[Customer ID]),
    "Detail Customer(Distinct)",
	CONCATENATEX(DISTINCT(SELECTCOLUMNS(FILTER(ALL(Sheet3),[Year&Month]=EARLIER(Sheet3[Year&Month])),"Custom ID",[Customer ID])),[Custom ID],","))

 

 

3. Create a table visual to show the result.

 

Regards,

Xiaoxin Sheng

  • hoggwildd's avatar
    hoggwildd
    Regular Visitor
    9 years ago

    Thank you both for your advice, time and help. I realize this is way over my head! I have now spent nearly 3 days on this and I am more confused then when I started. It seems so simple in my head! LOL

    All this time I thought I was getting pretty good at this too. This just shows me I really need to take a course.

     

    Thanks again,

    HW

    • chbraun's avatar
      chbraun
      Helper I
      9 years ago

      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

       

       

       

      • 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!