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