topic Re: Counting Dates in Customer Table Column, organised by Date Month in DAX Commands and Tips

Counting Dates in Customer Table Column, organised by Date Month

TelPowerBI104 — Tue, 10 Dec 2024 15:37:53 GMT

Hi PowerBi Community.

First Post.

I have a table of 13k Customers (Customer Table) and I have written a simple (Measured Column) query to look into the Orders Table to discover what date the first order was placed by each customer. This returns the date as a new column (First Order Date) in Customer Table. I also have a table to calculate all my calendar functions.

What I want to do now is tally all those dates in First Order Date to find how many new customers we have each month/year. I have crawled through the web trying various methods but I cannot get any to work so I wanted to create a fresh blog to ask for expert advice. I cna share more detail if needed . . .

Plese help 🙂

Terry

Re: Counting Dates in Customer Table Column, organised by Date Month

johnt75 — Tue, 10 Dec 2024 15:59:02 GMT

Link your calendar table to your customers table on the first order date column, and make sure that the calendar table is marked as a date table.

Then you can create a simple measure like

Num new customers = COUNTROWS( Customers )

and use that in visuals with columns from your calendar table.

Re: Counting Dates in Customer Table Column, organised by Date Month

Anonymous — Wed, 11 Dec 2024 08:49:12 GMT

Thanks for the reply from johnt75 , please allow me to provide another insight:

Hi, TelPowerBI104

johnt75's solution is a good idea.

To facilitate your understanding, I have created the following example:

1.Firstly, here is my sample data:

2.Secondly, I created the following measures:

Measure = COUNT('customer_data'[CustomerID])

Of course, there are many functions in DAX similar to the COUNT() function we are using. Please refer to:

3.Next, apply it to the visualisation object:

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Counting Dates in Customer Table Column, organised by Date Month

TelPowerBI104 — Wed, 11 Dec 2024 10:58:07 GMT

Thank you both solutions worked and increased my learning. I think I was over complicating my approach.