Product with no sales in last 90 days

[Anonymous]

Hello,

I want a DAX to create a new measure/column to give me a list of all active client who hasn't used/bought any product in the last 90 days. I have client name, status, date and sales column in my table. Now, I want to get the list of all client names in a matrix/table that hasn't done any sales in the last 90 days(by default), which will get updated based on the selected date range. 

Can anyone please help me with this?

Thanks
Shubs

[KHorseman]

Edit: for some reason when I went off to make my example I got "client" stuck in my head instead of "product." It works the same way obviously. Just replace the word client with product in everything you read here.

If I understand correctly, you're going to be selecting a range of 90 days by default, but the range can change so it could be within the last 10 days or 150 days or whatever range. I'm further going to make a couple of assumptions about your data model: you have a date dimension table, a sales fact table (with sales events and the dates of these sales) and it's connected to a client dimension table.

If you put the ClientName onto a matrix visual and use this measure, it will return those with no sales during the selected range:

No Sales = CALCULATE(
    DISTINCTCOUNT(ClientTable[ClientName]),
    FILTER(
        ADDCOLUMNS(
            ClientTable,
            "Sales",
            CALCULATE(
                SUM(SalesTable[SaleAmount])
            )
        ),
        [Sales] = 0
    )
)

Now, in my experience, this is a little ugly because it just counts them. You have to do something to return a value next to the clients though. You can't just return a name of a client because you'd end up with a single row with a single client. So I find in situations like this that my users like it if you use the opportunity to give some bonus information. Maybe something like, "for all the clients with no sales during the selected period, when was their last sale before that?"

Last Sale Date = CALCULATE(
    MAX(SalesTable[SaleDate]),
    ALL(SalesTable),
    FILTER(
        ADDCOLUMNS(
            ClientTable,
            "Sales",
            CALCULATE(
                SUM(SalesTable[SaleAmount])
            )
        ),
        [Sales] = 0
    )
)

You need the ALL(SalesTable) line there because you've selected the last 90 days in a slicer. You need a way to get out of those 90 days to find the last sales date before then, but when you're filtering out the clients you still want to use those 90 days. That's why I'm filtering ClientTable instead of filtering SalesTable in the first place. SalesTable is already filtered by your date selection. Make sense?

[Anonymous]

Hi @KHorseman ,

Thanks for the response. But, I can't share the data because of my contract. I have a single table(fact table) which has all the details including dates and one client table that contains all client details(master table). These both are connected with the Client ID.

Now, I want a DAX to get me the list of all active client in the selected date range which has no/zero sales in that period. I want the name of all those clients and not just the count of them.

Can you help me with it?

Thanks
Shubs