cancel
Showing results for
Did you mean:
Frequent Visitor

## Measure of new distinct clients per month.

I have to calculate how many new distinct clients I receive in my business monthly.
Here is an example table:

And the result should be like this:

New Customers = {3,2,3,0}

How can I reach this result using DAX?

1 ACCEPTED SOLUTION
Super User

@gtrani try a measure like this:

``````New Customers =
VAR __Month = MAX ( Customer[Month] )
VAR __PrevCustomers = CALCULATETABLE ( VALUES ( Customer[Customer] ),  ( 'Customer'[Month] < __Month ) )
VAR __CurrCustomers = VALUES ( Customer[Customer] )
VAR __NewCustomers = EXCEPT ( __CurrCustomers, __PrevCustomers )
RETURN
COUNTROWS ( __NewCustomers )``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

6 REPLIES 6
Super User

@gtrani you are not using best practices on how to manage date columns in the Power BI and that's why it is not working for you. That is a completely separate topic but for now if you change it to this, it will work:

``````New Customers =
VAR __Month = MAX (Planilha1[month])
VAR __PrevCustomers = CALCULATETABLE ( VALUES (Planilha1[customer]), FILTER ( ALLSELECTED( Planilha1),Planilha1[month]< __Month ) )
VAR __CurrCustomers = VALUES (Planilha1[customer])
VAR __NewCustomers = EXCEPT ( __CurrCustomers, __PrevCustomers )
RETURN
COUNTROWS ( __NewCustomers )``````

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

@parry2k
You are right!

I've created a Calendar table and now it works perfectly!

Thank you so much!

Super User

@gtrani works for me, share your pbix file:

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

@parry2k Please find it attached:

https://we.tl/t-B7EFI5YM2C

Thank you!

Super User

@gtrani try a measure like this:

``````New Customers =
VAR __Month = MAX ( Customer[Month] )
VAR __PrevCustomers = CALCULATETABLE ( VALUES ( Customer[Customer] ),  ( 'Customer'[Month] < __Month ) )
VAR __CurrCustomers = VALUES ( Customer[Customer] )
VAR __NewCustomers = EXCEPT ( __CurrCustomers, __PrevCustomers )
RETURN
COUNTROWS ( __NewCustomers )``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Hello @parry2k !

Did not work as expected.

The result using the code you suggested:

That's the same as a distinct count in each month.

The expected result was [3, 2, 3, 0].

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors