cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
krish003
Frequent Visitor

Power BI- New and Existing Customers Problem

 

community.PNGI 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 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors