cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PhillippaR
New Member

Complex query

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Complex query

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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

1 REPLY 1
Super User IV
Super User IV

Re: Complex query

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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors