Finding customers with no orders within a specifics date range

[murrayfoxcroft]

Hi all,

I have two tables in PowerBI and a slicer, presented below in an abstracted way.

I want to know the number of orders placed for a customer in a given date range. This data is a sample for illustration - there are actually around 10,000 Customers and 500,000 Orders and both tables have many other fields, Ids etc.

My Challenge - Whilst this is easy enough do by relating the tables and doing a count, the difficulty comes in when I still want to see customers with 0 orders and on top of that I want this to work within a date range. In other words, instead of the customers with no orders disappearing form the list, I want them to appear in the list, but with a 0 value, depending on the date range. It would also be good if this could act as a measure, so I can see the number of total customers that have not ordered on a month by month basis. I have tried outer joins, merge queries, cross joins and lookups and cant seem to crack it.

Example 1: If I set the order date slicer to be: 02/01/2017 to 01/01/2018 I want the following results

Example 2: If I set the order date slicer to be: 03/01/2017 to 06/01/2017 I want the following results

Any help much appreciated. 

Thanks 

[TomMartens · Accepted solution]

Hey,

this measure will do what you are loooking for and there is also a little pbix file available:

Counting Whats not there = 
IF(HASONEVALUE('Customer'[Customer]),
CALCULATE(
	SUMX(
		VALUES(Customer[Customer])
		,IF(
			ISBLANK(COUNTROWS('Sales'))
				,0
				,COUNTROWS('Sales')
		)
	)
)
,BLANK()
)

I guess you are aware that the list that you will contain always 10000 customers and for this reason it can take a while to fill the table visual, and maybe there is some potential to tweek the measure (but, this will take its time).

If you filter down the "possible" customers by using other attributes from the customer table, the measure should also work.

Be aware that my model contains three tables, Customer, Sales, and a separate Calendar Table (but this is a common practice)

Hopefully this is what you are looking for

Regards

Tom