Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table “Trended Data” that contains monthly data and is unique on Parts ID & Data As Of Date where Data As Of Date is my month end dates. I have another table “Completed Dates” that contains three columns Part ID, Completed Date, Cost. Completed Date does not have to be a month end date. I would like to have the ability to show two different things on a sheet using these two tables.
Sum(if([Data As Of Date]='$(=Date(MonthEnd(floor([Completed Date])),'MM/DD/YYYY'))', ([Cost]),null()))
I have listed an example of data for just two Part IDs (imagine I have a lot more than just 2).
Trended Data Table
Part ID | Data As Of Date | Projected Cost | Estimated Returns |
AAA | 3/31/2018 | 45 | 65 |
AAA | 4/30/2018 | 50 | 70 |
AAA | 5/31/2018 | 55 | 70 |
AAA | 6/30/2018 | 0 | 0 |
AAA | 7/31/2018 | 0 | 0 |
AAA | 8/31/2018 | 0 | 0 |
AAA | 9/30/2018 | 0 | 0 |
BBB | 3/31/2018 | 80 | 97 |
BBB | 4/30/2018 | 90 | 120 |
BBB | 5/31/2018 | 100 | 120 |
BBB | 6/30/2018 | 110 | 120 |
BBB | 7/31/2018 | 0 | 0 |
BBB | 8/31/2018 | 0 | 0 |
BBB | 9/30/2018 | 0 | 0 |
Completed Dates Table
Part ID | Completed Date | Cost |
AAA | 6/28/2018 | 60 |
BBB | 7/10/2018 | 105 |
I’m open to updating the loading script but I’d like to be able to develop some logic with a formula on a sheet because it would allow all users to be able to add more metrics on their own.
Thank you,
Brian