topic Re: Count returning customers who have orders every month for X number of months in DAX Commands and Tips

Count returning customers who have orders every month for X number of months

Anonymous — Sun, 27 Dec 2020 13:30:51 GMT

Hi,

I'm seeking help to get the count of customers who have placed orders at least once every month for the past X months.

For example, I am hoping to capture the count of customers who have orders in October, November and December (past 2 months and current month).

Any help and guidance is much appreciated.

Thanks,

Ashley

Re: Count returning customers who have orders every month for X number of months

littlemojopuppy — Sun, 27 Dec 2020 16:00:17 GMT

Hi! Here's some DAX for something similar I did...

VAR Weeks = 2 VAR CurrentWeek = CALCULATETABLE ( VALUES ( IdentityLog[IdentityId] ), DATESINPERIOD ( 'Calendar'[Date], DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( Weeks * 7 ) + 1, DAY ), 7, DAY ) ) VAR PreviousWeek = CALCULATETABLE ( VALUES ( IdentityLog[IdentityId] ), DATESINPERIOD ( 'Calendar'[Date], DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( ( Weeks - 1 ) * 7 ) + 1, DAY ), 7, DAY ) ) VAR TwoWeeksAgo = CALCULATETABLE ( VALUES ( IdentityLog[IdentityId] ), DATESINPERIOD ( 'Calendar'[Date], DATEADD ( LASTDATE ( 'Calendar'[Date] ), ( ( Weeks - 2 ) * 7 ) + 1, DAY ), 7, DAY ) ) RETURN COUNTROWS ( CALCULATETABLE ( VALUES(IdentityLog[IdentityId]), CurrentWeek, PreviousWeek, TwoWeeksAgo ) )

The measure above is intended to get the count of users who have logged in each week for three consecutive weeks. Replace my logic for weeks with current month, prior month and two months ago (PARALLELPERIOD() should make that very easy!).

Re: Count returning customers who have orders every month for X number of months

StefanoGrimaldi — Sun, 27 Dec 2020 17:30:02 GMT

hey this would do the trick (a measure):

Measure =

var a = 0

var b = 365

return

CALCULATE(DISTINCTCOUNT('orders report'[month value column]),(TODAY()-'Date Dim Table'[Date])<b,(TODAY()-'Date Dim Table'[Date])>=a)

this will determine if it its between last 365 days (1 year) and if so count by each different moth it exists on it, when applied to a table the filter will kick in with the desire visual)

note I use a date dimensional table, you can do it without it using the date in the fact table also.

Result of it:

if this was of help for your need let us know makring as soluction.

Re: Count returning customers who have orders every month for X number of months

amitchandak — Mon, 28 Dec 2020 05:33:54 GMT

Anonymous , This measure can give distinct month has sales , with help from date table

Example

Rolling 3 = CALCULATE(distinctcount('Date'[Month-Year]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0),-3,MONTH), not(isblank(Sales[Sales Amount])))

Count of customers having entry in three month

new Measure = countx(filter(Summarize(Sales, sales[Customer], "_1",[Rolling 3]),[_1]=3),[Customer])

Re: Count returning customers who have orders every month for X number of months

Anonymous — Tue, 29 Dec 2020 00:23:20 GMT

Thank you all for the solutions. I spent the last 2 weeks trying to find a solution myself on google search and got a solution within 1 day in this community. :). You guys are great!