I'm trying to calculate the total forecast proportional to the number of days active on contract. Each product has an annual target number though the contract can be part year, so its actual target will need to be adjusted proportionally. It's also common that a product can be on contract multiple times in a year, with date gaps in between. Occasionally, a new contract can be added with dates overlapping an existing contract, typically to extend the existing contract with new terms.
My thought process is to create a filtertable1 to capture product contracts meeting the date slicer criteria. Then create a filtertable2 to calculate avg, sum... but in filtertable2, how do I count the total number of days without counting the dates in gap? I'm totally stuck, and I'd really appreciate if someone can share some pointers. Thanks!
