So I have an Excel data source with three different sheets, all tied together on an ID field. I'm working with funnel data, in that the first sheet contains Lead information, the second sheet contains Opportunities, and the third sheet Conversions. I've been counting each measure by COUNT([Id]) for Leads, COUNT([id](Opportunities)) and COUNT([id](Conversions)) Each of these measures have different date values: [Lead Date] for leads, [Opportunity Date] for opportunities, and [Conversion Date] for conversions. An example calculation I want to make is a YOY difference of each of these measures but in one table, and I cannot use [Lead Date] for opportunities and conversions, since opportunities and conversions occur some amount of days AFTER [Lead Date].
I want a table with Year nested within Week Number (Week Start=Monday) for the rows with the measure names as the columns so I can do a YOY Difference calculation. But I need to use [Lead Date] for Leads, [Opportunity Date] for Opportunities, and [Conversion Date] for Conversions. So for example the table's row with Week 2 2017 would be 01/02/2017 for [Lead Date], 01/02/2017 for [Opp Date], and 01/02/2017 for [Conversion Date] until 01/08/2017. I can easily do this with three different tables in three different work sheets, but I want it all in one table. I would also like to add YOY growth (which should be easy once the YOY difference is able to be done) and I also want to create calculations for ratios of these measures: Opportunities/Leads, Conversions/Opportunities, and Conversions/Leads.
I think I need to create a calculation field, for instance called Cohort Week, where it would attribute [Lead Date] for Leads, [Opportunity Date] for Opps, and [Conversion Date] for conversions in the calculations. But then I also need a way to show the year: so it would be week 2: and then two rows for 2016 and 2017. I'm not sure if I'm overthinking this or what, but I'd really appreciate some help on this.