topic Re: Rolling customers repeat purchase check in current months- DAX in DAX Commands and Tips

Rolling customers repeat purchase check in current months- DAX

shankarshiva — Fri, 02 Dec 2022 11:25:37 GMT

Hello Experts

I am trying to calculate repeat purchase customer count of rolling 12M customers in current month.

Like if we are in Dec22 then rolling customer 12month will be Dec21 to Nov22, if we are in Nov22 then rolling 12month customer will be Nov21 to Oct22.

I am tying to get this by below DAX but output is coming incorrect.

Need your help please.

Repeat in Current Month of Rolling L12M customers =

Var _enddate=MAX('Calendar'[Date])

var _startdate=EOMONTH(_enddate,-13)+1

VAR _Rolling12MCustomers =

CALCULATETABLE('Secondary Data','Secondary Data'[New Key for Retailer count],FILTER(all('Calendar'),

'Calendar'[Date]>=_StartDate&&'Calendar'[Date]<=EOMONTH(_enddate,-1)))

Var _CurrentMonthSales=

CALCULATETABLE('Secondary Data','Secondary Data'[New Key for Retailer count],

FILTER(all('Calendar'),MONTH('Calendar'[Date])=MONTH(MAX('Calendar'[Date]))&&YEAR(('Calendar'[Date])=YEAR(MAX('Calendar'[Date])))))

Var RepeatCustomers=INTERSECT(_CurrentMonthSales,_Rolling12MCustomers)

return

COUNTROWS(RepeatCustomers)

amitchandak Greg PowerBI administrator Greg_Deckler PaulDBrown JihwanKim Jihwan_Kim Mikelytics lbendlin powerbiadmin PBICommunity PBCommunity

Thanks in advance

Re: Rolling customers repeat purchase check in current months- DAX

shankarshiva — Fri, 02 Dec 2022 11:31:37 GMT

Hello Experts

I am trying to calculate repeat purchase customer count of rolling 12M customers in current month.

Like if we are in Dec22 then rolling customer 12month will be Dec21 to Nov22, if we are in Nov22 then rolling 12month customer will be Nov21 to Oct22.

I am tying to get this by below DAX but output is coming incorrect.

Need your help please.

Repeat in Current Month of Rolling L12M customers =

Var _enddate=MAX('Calendar'[Date])

var _startdate=EOMONTH(_enddate,-13)+1

VAR _Rolling12MCustomers =

CALCULATETABLE('Secondary Data','Secondary Data'[New Key for Retailer count],FILTER(all('Calendar'),

'Calendar'[Date]>=_StartDate&&'Calendar'[Date]<=EOMONTH(_enddate,-1)))

Var _CurrentMonthSales=

CALCULATETABLE('Secondary Data','Secondary Data'[New Key for Retailer count],

FILTER(all('Calendar'),MONTH('Calendar'[Date])=MONTH(MAX('Calendar'[Date]))&&YEAR(('Calendar'[Date])=YEAR(MAX('Calendar'[Date])))))

Var RepeatCustomers=INTERSECT(_CurrentMonthSales,_Rolling12MCustomers)

return

COUNTROWS(RepeatCustomers)

@amitchandak @Greg @PowerBI @administrator @Greg_Deckler @PaulDBrown @JihwanKim @Jihwan_Kim @Mikelytics @lbendlin @powerbiadmin @PBICommunity @PBCommunity

Thanks in advance

Re: Rolling customers repeat purchase check in current months- DAX

amitchandak — Fri, 02 Dec 2022 14:46:10 GMT

shankarshiva ,

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

Rolling 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-12) ,-12,MONTH))

Lost Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Rolling 12) && not(ISBLANK([Rolling 12 before 12])) , 1,BLANK()))
New Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(ISBLANK([Rolling 12 before 12]) && not(ISBLANK([Rolling 12])) , 1,BLANK()))
Retained Customer This Period = Sumx(VALUES(Customer[Customer Id]),if(not(ISBLANK([Rolling 12])) && not(ISBLANK([Rolling 12 before 12])) , 1,BLANK()))

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

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 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

Re: Rolling customers repeat purchase check in current months- DAX

amitchandak — Fri, 02 Dec 2022 14:47:48 GMT

shankarshiva ,

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

Rolling 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-12) ,-12,MONTH))

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

Re: Rolling customers repeat purchase check in current months- DAX

shankarshiva — Sun, 04 Dec 2022 10:03:39 GMT

Hi Amit,

Thanks for quick reply.

I don't want to check rolling customer 12 before 12. I want to check only rolling 12M customers repeat in current month only. but as I am seeing you have taken 12 before 12 also which I think incorrect.

Request you please help.

Thanks..

Re: Rolling customers repeat purchase check in current months- DAX

shankarshiva — Thu, 08 Dec 2022 10:00:56 GMT

still waiting for response.

administrator pbiadmin Anonymous Member123456

Re: Rolling customers repeat purchase check in current months- DAX

shankarshiva — Thu, 08 Dec 2022 10:02:02 GMT

MicrosoftADE

Re: Rolling customers repeat purchase check in current months- DAX

v-jianboli-msft — Fri, 06 Jan 2023 08:05:15 GMT

Hi shankarshiva ,

Based on your description, I have created a simple sample:

Please try:

Measure = var _a = EOMONTH(MAX('Calendar'[Date]),-1) var _b = EOMONTH(MAX('Calendar'[Date]),-13) var _c =SUMMARIZE('Table','Table'[Column],"Repeat",IF(NOT(ISBLANK(CALCULATE(COUNT('Table'[Column]),FILTER(ALL('Table'),[Date]<=_a&&[Date]>_b&&[Column]=EARLIER('Table'[Column]))))),1,0)) return SUMX(_c,[Repeat])

Final output:

Best Regards,

Jianbo Li

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