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 dateofse...
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