Making a filtered measure to show past customers who haven't made a purchase this month, via tooltip

[rainynights]

So the use-case is this:

Let's say I want to know instantly who my drop-offs are. I have a starting table like this, tracking purchase behaviors of 6 unique customers, by the month that they made any purchase:

From the above table, we then derive the following visual, and add along a very basic tooltip that shows you who the customers were that made a purchase in that month:

Above, we simply see the distinct count of customers per month listed, and when I mouseover I see who they were. 

Now - I want to add 1 twist to this. Instead of the tooltip showing me the customers from that month, I want it to show me the customers from all the previous months, who did not make a purchase in that month.

E.g. if I moused over September, I would see the names of Mary and Jane, as these are past customers who did not make a purchase in September. It's tough to figure out even where to begin trying this!

Here's the sample PBIX file used to generate the above 2 screenshots:
https://1drv.ms/u/s!AqOYqK3B8ETjb0c6OL3DhtRbm94?e=CQuoCJ

[rajulshah · Accepted solution]

Hello @rainynights

Please use the measure as below in the tooltip table:

Customers =
VAR SelectedMonth =
    SELECTEDVALUE ( 'Table'[Start of Month] )
VAR CustomersPresent =
    CALCULATETABLE (
        VALUES ( 'Table'[Customer] ),
        FILTER (
            ALL ( 'Table'[Start of Month] ),
            'Table'[Start of Month] = SelectedMonth
        )
    )
RETURN
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Table'[Customer] ),
            'Table'[Customer],
            UNICHAR ( 10 )
        ),
        FILTER (
            ALL (
                'Table'[Start of Month],
                'Table'[Customer]
            ),
            'Table'[Start of Month] <> SelectedMonth
                && NOT ( 'Table'[Customer]
                IN CustomersPresent )
        )
    )

Hope this helps. If it doesn't, please let me know.

[rainynights]

@rajulshah 

Now that I think about expanding this feature further:

Is there any way to get the information to show in the form of a table, as opposed to a concatenated string with line breaks?

For instance, in case I also want to include the date of last purchase of that customer in a tooltip! Seems like a very interesting use-case!

[rainynights]

Hi @rajulshah ,

Thanks a lot for the help!

One correction I made to that was I changed 

'Table'[Start of Month] <> SelectedMonth 
to:

'Table'[Start of Month] < SelectedMonth

So on mouseover, it excludes future customers! But that is perfect for my needs. I have never used the CONCATENATEX function before, so that was interesting to take a look at too!