Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hoggwildd
Regular Visitor

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:

 

DateCustomer IDApt setConfirmedissuedsatResultSale Amount
2/27/2017 5:18:48 PM3975001100ROC             
2/28/2017 9:03:05 PM39750011101Leg            
2/27/2017 6:04:14 PM3975011100CTC             
2/27/2017 5:48:22 PM39750211113 
3/6/2017 7:05:05 PM3975021100ROC             
3/8/2017 2:39:46 PM3975021100Unissued       
3/9/2017 10:08:13 AM39750211113 
2/27/2017 12:57:33 PM3975031000ROC             
3/2/2017 4:09:14 PM3975031100CNC             
2/27/2017 6:00:03 PM3975041110CTC             
3/14/2017 5:14:40 PM3975041000Verif           
2/27/2017 6:15:12 PM3975051100CTC             
2/27/2017 6:01:54 PM39750610101Leg            
2/27/2017 1:07:50 PM3975071011Sale           21170.6
3/6/2017 3:57:19 PM3975081111Sale           13387
3/7/2017 12:17:35 PM3975091110NC              
3/8/2017 9:38:57 AM39750911113 
3/18/2017 12:12:22 PM3975091100CTC             
2/27/2017 5:55:42 PM3975101000ROC             
3/6/2017 2:24:53 PM3975101100COC             
2/27/2017 2:21:20 PM3975111100ROC             
3/6/2017 2:10:11 PM3975111111Sale           12887
3/9/2017 4:11:31 PM39751111113 
2/27/2017 1:26:06 PM39751211101Leg            
2/27/2017 1:27:45 PM3975131100ROC             
3/1/2017 5:22:00 PM3975131110NH              
2/27/2017 2:02:50 PM3975141100ROC             
3/1/2017 1:42:26 PM3975141100COC             
2/28/2017 8:01:00 AM39751511115 

 

So, if I want to know how many customer's (customer ID) contacted us in a month I would not want to count everytime we talked to them, only the first time. I.E. customer ID 397500 we spoke to on 2/27 and again on 2/28. Since it is the same customer I would only want to count it one time, for the 27th. Any help, suggestions or ideas are greatly appreciated!

 

HW

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@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...

Query Editor - Group By.gif

 

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

Query Editor - Group By ALT.gif

 

Hope this helps! Smiley Happy

Let me know if you have any questions!

 

View solution in original post

16 REPLIES 16
v-shex-msft
Community Support
Community Support

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.

Capture.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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

 

 

summarize table.JPG 

Sean
Community Champion
Community Champion

@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...

Query Editor - Group By.gif

 

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

Query Editor - Group By ALT.gif

 

Hope this helps! Smiley Happy

Let me know if you have any questions!

 

HPC
Regular Visitor

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

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.

Really Nice. Thanks.

Hi JacksonAndrew, Would you please explain why "1" is used as the expression in "Firstnonblank" function? Thank You.

Anonymous
Not applicable

bomb.com

poojik
Frequent Visitor


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?

POWERbi-SAMPLEDATASET.png

chbraun
Helper I
Helper I

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

 

Christian,,

 

Thank you for the time and the reply

 

We are a sales organization and we contact customers repeatedly, month in and month out. If we use the distinctcount would it count the same customer next month (or 6 months from now) again the first time we contacted them in THAT month?

 

As for the month period, sometimes we look at quarters, years, etc. I have a timeline built in that allows us to set the parameters as desired. The month was not important and I should have omitted it. Sorry for the confusion. 

 

HW

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 setdistinct count
114
Grand Total14

That is using a timeline slicing only to February, and that is accurate. But when I make the timeline March I get:

 

Apt setdistinct count
19
Grand Total9

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.