Calculate new customers for the month and respective sales

[Tanushree_Kapse]

Hi All,

I need help in calculating the new customers for every month.

Below is the sample data:

CustName	MonthYear	Cash	Investments	Deposit
A	Jan 2020	10	20	11
B	Jan 2020	30	13	12
A	Feb 2020	12	30	12
B	Feb 2020	14	45	10
C	Feb 2020	10	12	20
D	Feb 2020	12	14	15
E	Feb 2020	23	34	5

1. I need to calculate the distinct new customers count for the month and also the respective sum of cash, investment and deposit
For ex: From data: 
New customer for Jan:  2 ( A and B)
New customer for Feb: 3 ( C, D, E)

Expected result: 

MonthYear	NewCustomers	Cash	Investments	Deposit
Jan 2020	2	40	33	23
Feb 2020	3	45	60	40

Any help would be appreciated.

Thanks.

[amitchandak · Accepted solution]

@Tanushree_Kapse , refer to my blog on same topic

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

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

[amitchandak · Accepted solution]

@Tanushree_Kapse , refer to my blog on same topic

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

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retentio...

[Tanushree_Kapse]

Hi @amitchandak ,
Yes, that helped. I am able to calculate the new customers using the below DAX: 

New Customers count=

var aMTD = calculate(sum(FACT_TABLE[INVESTMENTS]),datesmtd(DateSlicer[MONTH]))

var bLMTD = calculate(sum(FACT_TABLE[INVESTMENTS]),DATESMTD(DATEADD(DateSlicer[MONTH],-1,MONTH)))   

var newcust = sumx(VALUES(FACT_TABLE[CLIENTNAME]), if(ISBLANK(bLMTD) && not(ISBLANK(aMTD)) ,1,0))

return newcust

But, I also want to calculate the corredsponding Cash, Investments, deposits, made by these new customers for that month.

MonthYear	NewCustomers	Cash	Investments	Deposit
Jan 2020	2	40	33	23
Feb 2020	3	45	60	40

Can you please help.

[amitchandak]

@Tanushree_Kapse , In the last  measure do some change

example

new customer cash =

sumx(VALUES(FACT_TABLE[CLIENTNAME]), if(ISBLANK(bLMTD) && not(ISBLANK(aMTD)) ,[Cash],blank()))

new customer Deposit=

sumx(VALUES(FACT_TABLE[CLIENTNAME]), if(ISBLANK(bLMTD) && not(ISBLANK(aMTD)) ,[Deposit],blank()))

[Tanushree_Kapse]

\Hi @amitchandak ,

New Investment=

Var currmonth= CALCULATE(DISTINCTCOUNT(FACT_TABLE[CLIENTNAME]),DATESMTD(DateSlicer[MONTH]))

Var prevmonth= CALCULATE(DISTINCTCOUNT(FACT_TABLE[CLIENTNAME]),DATESMTD(dateadd(DateSlicer[MONTH],-1,MONTH)))

return

sumx(VALUES(DateSlicer[MONTH]), if(ISBLANK(prevmonth) && not(ISBLANK(currmonth)) ,calculate(sum(FACT_TABLE[INVESTMENTS]),ALL(FACT_TABLE[CLIENTNAME])),Blank()))

I tried this, but it's just giving me the values for Jan 2020, for other months it is blank.

[Tanushree_Kapse · Accepted solution]

Tried this:
This is giving the values for respective month.
invNew =

VAR currentCustomers = VALUES(FACT_TABLE[CLIENTNAME])

VAR currentDate = MIN(DateSlicer[MONTH])

VAR pastCustomers = CALCULATETABLE(VALUES(FACT_TABLE[CLIENTNAME]),

ALL(DateSlicer[MONTH])

, DateSlicer[MONTH]<currentDate)

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN SUMX(newCustomers, CALCULATE(SUM(FACT_TABLE[INVESTMENTS])))

Thanks @amitchandak , for your refrences, it helped a lot.