Complex query

[PhillippaR]

Hi all

Am trying to do a query which is a little complex and beyond my capabilities. It's for CRM sales data so I have an account table and an opportunity table. The end goal is to show 'new accounts' this financial year however an account can be created long before it purchases anything. So what I want is a query that determines the date the first opprotunity was won for an account and only include those in this financial year.

As an account may have many opportunities, it's important to only take the first (oldest) one to see when the account became a customer.

I'm sure it's doable through sorting/grouping but a little unsure how to go about it. Any ideas?

Cheers

P

[Greg_Deckler · Accepted solution]

Is this Microsoft CRM or some other CRM? Sample data is always helpful. The basic approach would be that you relate your account and opportunities and then you could create a measure that creates a count of opportunities filtered such that it only counts opportunities in the last 365 days or whatever time measure you want to use. Then, you list your accounts and filter where that count is not 0. Now you only have accounts where the opportunities closed in the last x number of days/timeline are not 0 so those are new accounts.

You would create a measure in the Accounts table that counts related Opportunities, if that is not clear.