Distinctcount active customers based on Sales 12M

[Linger]

Hi Power BI Community,

I have a requirement that I just can't get my head around.

As an example I have used a calendar table and a simple data table with the columns: Customer ID, Date and Sales

Basically I want to count all active customers in a month. However an active customer is defined as a customer with sales >0 in the last 12 months.

To calculate the sales in the last 12 months I use this measure which works fine:

Sales 12M = CALCULATE(SUM([Sales]);DATESINPERIOD('Calendar'[Date];MAX('Calendar'[Date]);-12;MONTH))

So if I select 2020-03 for example I get the correct result for Sales 12M.

Now I would like to count the distinct no. of customers for that month with sales 12M >0.

This formula kind of works but not exactly as it should:

No. of Active Customers = CALCULATE(DISTINCTCOUNT(Data[Customer ID]);FILTER(VALUES(Data[Customer ID]);[Sales 12M]>0))

It only works if a customer has sales in the selected month. If it doesn't have sales in that month, there is no data entry and it is not included in the table that VALUES returns.

Still it could be an active customer because it could have sales in any other of the 12 months being considered for Sales 12M.

I have played around with ALL and ALLSELECTED but nothing seemed to work as it should.

Of course I could just do a simple Sales >0 filter but there can be instances in which a customer buys something lets say in January for 500€ which makes him active for the next 12M but then he returns it in May which is recorded as -500€. So until April Sales 12M returns 500€ and then 0€ which is fine but if I just filter my Sales column with >0 the active customer figure is not correct. That's why the aggregated measure must be used as a filter.

How can I get my formula to run the DISTINCTCOUNT for all customers that have Sales 12M returned and not only the ones with sales in the selected month?

I have recently had a go at SUMMARIZE which worked great but I only had to aggregate a full year. Is it possible to aggregate a period of 12 months within SUMMARIZE based on the date selection?

This is just an idea I had that might work but perhaps there is another solution, too.

Thanks a million for any help with this and sorry if I missed an existing post, I couldn't find one describing my exact problem.

Also if you need any further explanations just let me know 😊

[Anonymous · Accepted solution]

[# Active Cust] =
var __activeCusts =
	FILTER(
		Customers, // must be a dimension
		[Sales 12M] > 0
	)
return
	COUNTROWS( __activeCusts )

Best

D

[Linger]

Hi darlove,

Thank you so much. I haven't really worked with variables so far, guess I should learn more about those!

I created a separate table with unique Customer IDs and used that as the table for Filter which worked out great 😀