Count customers returning after three months of inactivity

[dhooger]

I'm looking to write DAX to calculate a count of customers buying at least 3 units this month that have not purchased in the three previous months. The count should work at the month level and should sum at the year level.

Examples:

Customer A has never purchased and in Jan 2018, they buy 3 units. Thus they would be a new customer in Jan 2018. They then buy 3 additional units again in May 2018. This is a new customer in May 2018. If you summed the new customers in 2018, it would still only be one new account because there is only one store.

Customer B has never purchased and in Jan 2018, they buy 1 unit. Thus they would NOT be a new customer in Jan 2018. They then buy 2 additional units again in May 2018. This is NOT a new customer in May 2018. AND, even though they have now bought a total of 3 units in 2018, we do not consider them a new account for the year because there was not a purchase of 3 units in a given month.

[dhooger]

Sample data:

Date	AccountID	ProductID	Qty
1/1/19	A	1001	3
5/1/19	A	1003	3
7/1/19	A	1001	3
1/1/19	B	1001	1
1/1/19	B	1002	1
1/1/19	B	1003	1
5/1/19	B	1001	1
6/1/19	B	1002	2
7/1/19	B	1003	3

Result expected by month:

Month Level	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19
New and/or returning customers	2				1		1

Both A and B bought 3 units in Jan and had never bought before.

A bought 3 units in May, which is more than 3 months from Jan so they get a 1 in the May column

A bought 3 units in July, but that is not 3 months from May so they do NOT get a 1 in July.

B bought some units in May and June but not 3 at one time so they do not count in either month.

B did buy 3 units in July and that is more than 3 months from Jan so they get a 1 in the July column.

Expected result when looking at year level:

Year Level	2019
New and/or returning customers	2

Even though both A and B qualified in individual months more than one time, they are only shown here once each per year.

[v-diye-msft · Accepted solution]

Hi @dhooger ,

Let me know if you'd like to get below results:

pbix attached: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EdISJcm05M9PqOiPPeeHej...

[BIW]

Hi @v-diye-msft ,

Would you mind posting the URL again?  I got error message when clicking on https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EdISJcm05M9PqOiPPeeHej...

Many Thanks!

[dhooger]

This is great and I've added to it by replacing Measure 3 with:
COUNTROWS (
FILTER (
SUMMARIZE (
'SRS Sales',
'SRS Sales'[dim_srs_outlet_key],
'SRS Sales'[Date],
"SumM2", [Measure 2]
),
[SumM2] >= 1
)
)

But, now I have a different problem. In the sample data, I assigned things to the same date in a month to represent the month. We have sales throughout the month that we have to summarize for the month. I've used STARTOFMONTH as a calculated column but still not quite getting there. Getting failure on what you had as Measure to work.

[v-diye-msft]

Hi @dhooger ,

Please kindly mark the helpful answer as solution regarding to your previous question, for the new question, could you please create a new thread?

[Greg_Deckler]

@marcorusso wrote a quick measure for this: https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297?searc...