Hi,
I have a table containing customer name, invoice value and week ending date (as well as several other columns). A customer can have multiple invoices for the same week ending date and will have invoices over several weeks or months.
I am fairly new to DAX so am struggling a bit with when I feel may be fairly simple.
I want to identify how many new customers I have based on the week ending date. A new customer would have their first invoice(s) in that week and I would like to use a measure to do this.
I have got this to work by adding a column to the table with a '1' where the week ending date for a customer is the min(week ending date) and the created a measure to count distinct customer names where that column=1.
What I am trying to work out is how to do this without adding the column to my table, how can I do this with just DAX.
Hope you can help
Hi Brian,
Can you please supply your file/data.
Regards
Phil
Hi Brian,
You should be able to use the COUNTDISTINCT function (session 9.05) to count the number of distinct customer IDs per week ending date. Put the week ending date in the row labels area for the measure.
If you get stuck, please share your file as Phil suggested.
Mynda
Thanks for the replies.
If I count distinct customer names, I get the number of customers I have invoiced each week but what I want to identify is how many new customers I have each week.
I have added a sample file. but have created this sample on my macbook - so no use for Power Query or Power pivot. It shows a sample of the data I have along with an example of what I have done with power query to add a column. The column identifies the first week with an invoice for each customer so I can then count distinct customer names where the new column has a '1' and I get the number of new customers each week.
5/1/24 - 2
19/1/24 - 1
23/2/24 - 1
I'd like to do this using DAX without having to add the column though.
Thanks, Ian.
Hi Ian,
You need to create a Calendar/Date table and create a relationship between your source data/fact table and the calendar table that includes the week ending date.
You then need two measures:
CustomerFirstDate: =CALCULATE(MIN(Table1[Week Ending Date]),Datesbetween(Calendar[Date],minx(all(Calendar),Calendar[Date]),max(Calendar[Date])))
New Customers: =COUNTROWS(FILTER(VALUES(Table1[Customer Name]),[CustomerFirstDate]>=min(calendar[Date])))
See example file attached.
Hi Mynda,
Thank you so much for your help, I was a long way away form getting that correct and I'm some way away from fully understanding your measures.
I have 101 questions but I'll get back to your course!
Thanks, Ian.
You're welcome, Ian. Keep working through the course and it will become clearer
