Measure of new distinct clients per month.

[gtrani]

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?

[parry2k]

@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

[gtrani]

@parry2k 
You are right!

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

Thank you so much!

[parry2k]

@gtrani works for me, share your pbix file:

[gtrani]

@parry2k Please find it attached:

https://we.tl/t-B7EFI5YM2C

Thank you!

[parry2k · Accepted 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 )

[gtrani]

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].