Hello Forum,
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
Hi @richmont ,
Give something like this a go.
I hope this is helpful
1 Like
Hi Melissa - Fantastic…this seems to be working! You are a lifesaver as I needed this for a presentation I have here in the US (in three hours). Plus, great that now I can study this solution and learn from it. You guys are great and keep up the good work…
1 Like
I may have spoke too soon. The formula does work, but it does not factor in those situations where the previous months are in the previous year. For example, September (9) is greater than May (5), but if the order date is September of the previous year it needs to be counted.
Hi @richmont ,
Just tested the wonderful formula of Melissa, it works properly, also over year-end, see table below for test examples. Or is there a misunderstanding from my site ?
OrderDueDate v3.pbix (131.3 KB)
Below is another possible way to calculate the requested Orders with a due date of minimal 2 months:
> DS Orders w 2 month duedate = CALCULATE( SUM( OrderT[Orderamount]),
> FILTER( OrderT, DATEDIFF( OrderT[Orderdate], OrderT[Due Date], DAY ) >60 ))
Kind regards, deltaselect
3 Likes
Hi @richmont did the response provided by @Melissa and @deltaselect help solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @richmont, we’ve noticed that no response has been received from you since the 29th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @richmont, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
