DAX-formula counting clients

[David_1970]

Hi, I want to be able to count the number of clients. However, if a new client becomes assigned to an already existing client group, that client should not be considered a new client. This is the case for clients 2 and 4 in the table below. Thus, in January one client was created; in February there were zero clients created; in June one client; and in October there were two new clients. Any suggestions for a DAX-formula?

Client group number	Client number	Creation date
1	1	2014-01-01
1	2	2014-02-01
3	3	2014-06-19
1	4	2014-06-23
5	5	2014-10-26
6	6	2014-10-28

[Zubair_Muhammad · Accepted solution]

Hi @David_1970

Try this solution

Add a calculated column as follows

Index =
IF (
    CALCULATE (
        COUNTROWS ( TableName ),
        FILTER (
            TableName,
            MONTH ( TableName[Creation date] )
                < MONTH ( EARLIER ( TableName[Creation date] ) )
                && TableName[Client group number] = EARLIER ( TableName[Client group number] )
        )
    )
        >= 1,
    BLANK (),
    1
)

Now Sum of this Index will give you desired RESULTS

Just Add Month and Sum of Index to a TABLE visual

[Zubair_Muhammad]

Hi @David_1970

File attached here

(with your sample data)