Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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?
Solved! Go to Solution.
@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.
@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!
@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.
@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:
That's the same as a distinct count in each month.
The expected result was [3, 2, 3, 0].
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
60 | |
58 | |
56 | |
38 | |
28 |
User | Count |
---|---|
82 | |
61 | |
45 | |
41 | |
39 |