Thinking out loud here, open to ideas, feedback and comments!
I have a table of Contracts data that contains Account #, Contract #, Contract Value, Start Date and Expiration Date. I have another table of work orders for said customers. Looking to compare Contract Value for the year to work order value for the year. The issue I have is the contracts can span 6 months, 12 months, 450 months, etc. So if I have a contract that spans 5/17/2016 - 6/17/2025, with a single overall value associated, how can I display just the value for the past 12 months? The quick and dirty I think would work would be adding a Months column to the pull (Datediff( MONTH, contract.startdate, contract.expirationdate) AS ‘MonthsinContract’.
From there I could divide my total by months and and get the monthly value. Multiply by 12 to get full year value. This will have its own limitations though. Any thoughts on how to attack this or am I going to be limited based on the way the data is presented as an overall value instead of a monthly value with a term? Sample contract data attached EDNA Contracts.xlsx (56.3 KB)