topic Re: Calculate new customers for the month and respective sales in DAX Commands and Tips

Calculate new customers for the month and respective sales

Tanushree_Kapse — Thu, 03 Feb 2022 05:52:45 GMT

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.

Re: Calculate new customers for the month and respective sales

amitchandak — Thu, 03 Feb 2022 05:54:27 GMT

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/ba-p/1361529

Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497

Re: Calculate new customers for the month and respective sales

Tanushree_Kapse — Thu, 03 Feb 2022 06:40:11 GMT

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.

Re: Calculate new customers for the month and respective sales

amitchandak — Thu, 03 Feb 2022 07:40:27 GMT

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()))

Re: Calculate new customers for the month and respective sales

Tanushree_Kapse — Thu, 03 Feb 2022 09:37:51 GMT

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

Re: Calculate new customers for the month and respective sales

Tanushree_Kapse — Thu, 03 Feb 2022 09:52:08 GMT

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.