Power BI- New and Existing Customers Problem

[krish003]

I am trying to calculate New Clients and Existing Clients every week. My data model is as shown in the figure:

billing_entries:

Has all Billing related information for Clients by dateofservice(dd/mm/yyyy)

The dates(dt) table is connected to billing(dateofservice),  where dates[dt] has dates from 1/1/1900 -1/1/2099

Clients table has ~(70) unique Client ID's; Billing(~90 unique clientID's) now, I'm trying to find out the information only about the clients in the Client table.

i.e. # of new clients that are visiting every week

new client: If the client_id doesn't exist in Billing Table for 365 days consecutively, It is considered new next time it shows up or if it's new entirely.

Returning Client: On a particular day, If the client_id is seen atleast once in last 365 days, then it is considered as returned client.

I came up with bunch of DAX calculations, but none of them seemed to work. 

New Clients = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(VALUES(billing_entries[clientid]), "Previous Billing",
            CALCULATE(COUNTROWS(billing_entries),
                FILTER(
                    ALL(dates),
                    dates[dt]<MIN(dates[dt])
                )
            )
        ),
        [Previous Billing]=0
    )
)
                
    

new_client v1 = COUNTROWS(
    FILTER(VALUES(billing_entries[clientid]),
        CALCULATE(
            COUNTROWS(billing_entries),
            FILTER(DISTINCT(ALL(billing_entries)),
                billing_entries[dateofservice]<MAX(billing_entries[dateofservice])))))

new_client_v2 = 
VAR First_time_attending = ADDCOLUMNS(
    ALL(billing_entries[clientid]),"First Attendance Date", CALCULATE(MIN(billing_entries[dateofservice]),ALLEXCEPT(billing_entries,billing_entries[clientid])))
VAR NewClient = FILTER(
    First_time_attending, CONTAINS(VALUES(dates[dt]),dates[dt],[First Attendance Date]))
RETURN
CALCULATE(SUM(billing_entries[chargeagreed]),NewClient)

I hope this gives some idea about the problem. I appreciate any help in solving this problem.

Thanks,

Krish

[v-shex-msft]

Hi @krish003,

I'm not so sure for your formula, maybe you can try to use following formula to calculate client id if it works on your side:

new client =
VAR temp =
    SELECTCOLUMNS (
        billing_entries,
        "clientid", [clientid],
        "current date", billing_entries[Date]
    )
VAR list =
    ADDCOLUMNS (
        temp,
        "Previous", MAXX (
            FILTER (
                temp,
                [clientid] = EARLIER ( [clientid] )
                    && [current date] < EARLIER ( [current date] )
            ),
            [current date]
        )
    )
RETURN
    COUNTROWS (
        DISTINCT (
            SELECTCOLUMNS (
                FILTER (
                    list,
                    [previous] = BLANK ()
                        || DATEDIFF ( [Previous], [current date], DAY ) >= 365
                ),
                "ID", [clientid]
            )
        )
    )

If above not help, can you please share some sample data to test and coding formula?

Regards,
Xiaoxin Sheng

[krish003]

Hi Sheng, 

The DAX doesn't work; I was able to figure our a temp work around for the problem by creating a new dummy table and extract the required data. Let me send you some sample data so that you'll have better idea.

Thanks

Krish

[v-shex-msft]

HI @krish003,

You can upload to onedrive or google drive, then share link here.

Notice: I not need real data, if you table contains sensitive data, please create some test data with similar table structure and relationship for test.

Regards,

Xiaoxin Sheng