How to calculate new customers and repeated customers?

[bourne2000]

Hi 

I am having below table, which consists of customer ID and date. I calculated week number and weekday.

I would like to calculate new customer and repeated customers weekly basis. I need to use login date to calculate new customers and repeated customers. I checked other posts but no success

Can anyone advise me?

Attached is the sample pbix file https://we.tl/t-vF9b2y6IHg

[v-zhangti · Accepted solution]

Hi, @bourne2000 

I have done the following operations on the basis of your attached pbix file.

Three calculated columns have been added.

Count = 
CALCULATE (
    COUNT ( sessions[customer_id] ),
    FILTER (
        sessions,
        [Week Num] <= EARLIER ( sessions[Week Num] )
            && [customer_id] = EARLIER ( sessions[customer_id] )
    )
)

Count = 
CALCULATE (
    COUNT ( sessions[customer_id] ),
    FILTER (
        sessions,
        [Week Num] <= EARLIER ( sessions[Week Num] )
            && [customer_id] = EARLIER ( sessions[customer_id] )
    )
)

New or Old = 
IF ( [Count] = [Min], "New", "Old" )

Take customer_id equals 6 as an example, when the Min column equals the Count column, it means he is the first time to appear, so New is output.

Measure:

New = 
CALCULATE (
    DISTINCTCOUNT ( sessions[customer_id] ),
    FILTER (
        sessions,
        [New or Old] = "New"
            && [Week Number] = MAX ( sessions[Week Number] )
    )
)

Old = 
CALCULATE (
    DISTINCTCOUNT ( sessions[customer_id] ),
    FILTER (
        sessions,
        [New or Old] = "Old"
            && [Week Number] = MAX ( sessions[Week Number] )
    )
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@bourne2000 , use a date table and create week there with Week rank

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Then have measure like

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

New Customer = COUNTX(filter(Customer, ISBLANK([Last Week]) && Not(ISBLANK([This Week]))) , Customer[Name])
Lost Customer = COUNTX(filter(Customer, not(ISBLANK([Last Week])) && (ISBLANK([This Week]))) , Customer[Name])
Retained Customer = COUNTX(filter(Customer, not(ISBLANK([Last Week])) && NOT(ISBLANK([This Week]))) , Customer[Name])

refer

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...