Forum Discussion

krish003's avatar
krish003
Frequent Visitor
8 years ago

Power BI- New and Existing Customers Problem

 

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      krish003
      Frequent Visitor
      8 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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