I have a table called "Opps Production" where all sales data is listed. One column, "Organization" Does not have unique values because our clients (Organizations) have multiple sales. Each sale is also associated with a job number. So Client "X" could have multiple sales over the course of years/months
Organization | Job Number | Sale Value $
X| 2017-DLL-J01| $100,000
X|2016-DLL-J09 | $40,000
I'm trying to write a measure or a column to show new clients. That is, any Sale that is the first for a particular "Organization."
I need to be able to create a filter that says "show me any new client where the sale was made in the last 90 days"
Could you grab the MIN and MAX of the date by client and compare them, if they are equal, then that is a new customer? You would probably have to create a custom column where you parse your Job Number out into a date field, it might get a little ugly given that format below, buy potentially doable.
@Greg_Deckler unfortunately no, that won't work. When a new client has a sale made, sometimes multiple jobs are executed at once. They can purchase a recurring service agreement in addition to a one time project. So a new client could potentially have multiple jobs within the span of a few days but still be considered "new."
Sounds like you would instead need to define the span of time that a customer could be "new" and then take the MIN of the date and do a DATEDIFF with TODAY() and see if it is greater than a certain number. Something like: