Rolling customers repeat purchase check in current months- DAX

[shankarshiva]

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

[v-jianboli-msft · Accepted solution]

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.

[shankarshiva]

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

[amitchandak]

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

[shankarshiva]

@MicrosoftADE 

[shankarshiva]

still waiting for response.

@administrator @pbiadmin @pbix @Admin 

[amitchandak]

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

[shankarshiva]

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