values from current month not present in previos dates

[Anonymous]

I have made a table visual below:

For each Sales Representative I want to calculate no of distinct clients in a month which are not present in dates before the particular month.

I wrote following measure but I am getting blank values:

New Clients =

var _mindate = MIN(Payments[TransactionDate])

var _maxdate = MAX(Payments[TransactionDate])

VAR CurrentMonthDistinctClients = CALCULATETABLE(VALUES(Contacts[UniqueID]),FILTER(Payments,Payments[TransactionDate]>=_mindate && Payments[TransactionDate] <= _maxdate))

VAR PreviousALLMonthsDistinctClients = CALCULATETABLE(VALUES(Contacts[UniqueID]),FILTER(Payments, Payments[TransactionDate] < _mindate))

Var NewClients = COUNTROWS(EXCEPT(CurrentMonthDistinctClients,PreviousMonthsDistinctClients))

RETURN

NewClients

Any help is appreciated.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

My Sample is as below. I create a relationship between Fact table and Date table by Date column.

Fact Table:

Date table:

Date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "MonthYear", FORMAT ( [Date], "MMM 'YY" )
)

Try this code.

New Clients = 
VAR _RANGESTART = CALCULATE(MIN('Date'[Date]),ALLSELECTED('Date'))
VAR _RANGEEND = CALCULATE(MAX('Date'[Date]),ALLSELECTED('Date'))
VAR _CLIENT_LIST_BEFORE = CALCULATETABLE(VALUES('Table'[UniqueID]),FILTER(ALL('Table'),'Table'[Date]<_RANGESTART&&'Table'[Sales Representative]=MAX('Table'[Sales Representative])))
RETURN
CALCULATE(COUNT('Table'[UniqueID]),FILTER('Table',NOT('Table'[UniqueID] IN _CLIENT_LIST_BEFORE)))

Here I select my date slicer between 2021/03/01 to 2020/12/31.

Client in "A G" before date range are {C1,C2,C3}, so number of new client is 0.

Client in "C U" before date range are {C1,C2,C3,C5}, so number of new client (C4,C6) is 2.

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , refer these two they can help

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

file is attached after signature