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
gtrani
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:

gtrani_0-1695064187061.png

 

And the result should be like this:

gtrani_1-1695064354207.png

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

 

How can I reach this result using DAX?

1 ACCEPTED SOLUTION
parry2k
Super User
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.

View solution in original post

6 REPLIES 6
parry2k
Super User
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

 

Add Date Dimension
Importance of Date Dimension
Time Intelligence Playlist

 



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.

@parry2k 
You are right!

 

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

 

Thank you so much!

parry2k
Super User
Super User

@gtrani works for me, share your pbix file:

 

parry2k_0-1695139935606.png

 



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.

@parry2k Please find it attached:

https://we.tl/t-B7EFI5YM2C

 

Thank you!

parry2k
Super User
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.

Hello @parry2k !

Did not work as expected.

The result using the code you suggested:

gtrani_0-1695125524989.png

 

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors