Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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].
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
88 | |
70 | |
66 |