Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shankarshiva
Frequent Visitor

Rolling customers repeat purchase check in current months- DAX

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)
Thanks in advance
1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

Hi @shankarshiva ,

 

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

vjianbolimsft_0-1672992231355.png

vjianbolimsft_1-1672992242937.png

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:

vjianbolimsft_2-1672992273587.png

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.

View solution in original post

7 REPLIES 7
v-jianboli-msft
Community Support
Community Support

Hi @shankarshiva ,

 

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

vjianbolimsft_0-1672992231355.png

vjianbolimsft_1-1672992242937.png

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:

vjianbolimsft_2-1672992273587.png

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
Frequent Visitor

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)
Thanks in advance

@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
Frequent Visitor

shankarshiva
Frequent Visitor

still waiting for response.

 

@administrator @pbiadmin @pbix @Admin 

amitchandak
Super User
Super User

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors