I have a table that contains several other measures such as the sum of CRM opportunities (forecast qty), and the sum of shipped qty. Now, I need to add the sum of ordered qty.
The catch is that order qty needs to be based on orders that are equal to/greater than 2 months before the due date. For example: Assume we have orders with the following due dates and order dates.
Order and due date both live in the Orders table…but only due dates is connected to my date table.
The only values that I want to sum are those with an order date 2 months (or more) before the due date month. So in this example, I would only want to sum up the quantities for the months with order date months of Jan and Feb since these are two months (or more) than the due date month.
Order Date Due Date Qty Ordered
Jan 3 April 15 345
Jan 24 April 3 89
Feb 5 April 8 876
Feb 18 April 27 732
Mar 5 April 23 458
April 9 April 18 762
Ideally, I need the formula in a measure, not a calculated column