I have a dataset that holds data regarding Leads and Opportunities. Opportunity data linked to its originating Lead is displayed as one row in the dataset. There is a lead creation date and an opportunity creation date.
This data set has been created in SQL joining the LeadID from the Lead table, to the Originating LeadID in the Opportunities table.
I have been asked to show all opportunities where the created date on the opportunity happened in the same period as the created date of the lead. The date is being filtered by a calendar table.
An example would be that I have selected July 21, August 21 and September 21 in the filter. I would like a count of the number of rows where the created date for the lead and the created date for the opportunity fell within these months.
I have tried using USERELATIONSHIP but that isn't using my relationships simultaneously, it just makes the active relationship, inactive and makes the inactive relationship, active.
Below is the Relationships I have created.
I'm thinking that what I am trying to achieve is not possible but was wondering if there is a better way of modeling the data to achieve the same outcome.
Thank you for your time. Any help is greatly appreciated.