Counting Dates in Customer Table Column, organised by Date Month

[TelPowerBI104]

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

[johnt75 · Accepted solution]

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.

[TelPowerBI104]

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

[Anonymous · Accepted solution]

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:

COUNT function (DAX) - DAX | Microsoft Learn
COUNTA function (DAX) - DAX | Microsoft Learn
COUNTAX function (DAX) - DAX | Microsoft Learn
COUNTBLANK function (DAX) - DAX | Microsoft Learn
COUNTROWS function (DAX) - DAX | Microsoft Learn
 

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.

[johnt75 · Accepted solution]

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.