Show customers with no order in X months

[hobojoe720]

Hey all,

2 parts to this really; ideally I would like to have the X months selectable from a slicer. But in effect, we have a table similar to this:

+-----------+------------+----------+
| Customer  | OrderDate  | SalesRep |
+-----------+------------+----------+
| Customer1 | 2019-09-02 | John     |
+-----------+------------+----------+
| Customer2 | 2019-05-01 | Sally    |
+-----------+------------+----------+
| Customer1 | 2018-01-01 | John     |
+-----------+------------+----------+
| Customer2 | 2018-01-01 | Sally    |
+-----------+------------+----------+
| Customer3 | 2015-01-01 | Bob      |
+-----------+------------+----------+

We have a list of orders, with a date, a sales rep and a customer name. What we want to be able to do is show all customers who's latest order is older than X months. So for example, if we set the above to 3 months (today being 2019-09-02) Customer2 and Customer3 would show, with the latest order date and the SalesRep.

+-----------+------------+----------+
| Customer  | OrderDate  | SalesRep |
+-----------+------------+----------+
| Customer2 | 2019-05-01 | Sally    |
+-----------+------------+----------+
| Customer3 | 2015-01-01 | Bob      |
+-----------+------------+----------+

Change it to 6 months and only Customer3 would show.

+-----------+------------+----------+
| Customer  | OrderDate  | SalesRep |
+-----------+------------+----------+
| Customer3 | 2015-01-01 | Bob      |
+-----------+------------+----------+

Can anyone advise?

Thanks in advance.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[ani87]

Thank you so much. It helped me a lot!

[Greg_Deckler]

You should be able to create a measure and filter on it like this:

Measure = 
VAR __months = MAX('Slicer'[Value]) // get slicer value
VAR __customer = MAX([Customer]) // get current customer
VAR __table = FILTER(ALL('Table'),[Customer] = __customer)
VAR __maxDate = MAXX(__table,[OrderDate])
VAR __isInScope = IF(__maxDate >= EDATE(TODAY(),__months),1,BLANK())
RETURN
__isInScope

Something along those lines.