Hi all,
I have a question regarding the calculation of accumulating % totals.
I have a table that has a list of quotes, quoted quantities, and quoted dates. I have another table with a list of orders, order quantities, and order dates. The link between the two tables are the ID number of the quote. The tables can look something like this:
Quote ID | Product | Qty | Quoted Date |
2055F | A | 40 | Jan 2, 2024 |
2055F | B | 15 | Jan 2, 2024 |
2055F | C | 25 | Jan 2, 2024 |
2045A | A | 10 | Jan 15, 2024 |
2045A | D | 5 | Jan 15, 2024 |
2000C | A | 8 | Jan 8, 2024 |
2000C | B | 7 | Jan 8, 2024 |
2000C | C | 20 | Jan 8, 2024 |
2000C | D | 50 | Jan 8, 2024 |
Quote ID | Product | Qty Ordered | Order Date |
2055F | A | 20 | Jan 5, 2024 |
2055F | B | 5 | Feb 3, 2024 |
2055F | C | 15 | Jan 18, 2024 |
2055F | B | 10 | Feb 8, 2024 |
2045A | A | 8 | Jan 30, 2024 |
2045A | D | 3 | Feb 2, 2024 |
2000C | A | 8 | Jan 20, 2024 |
2000C | B | n/a | n/a |
2000C | C | 15 | Jan 10, 2024 |
2000C | D | 35 | Jan 30, 2024 |
What I’d like to understand is how many days it takes to get <25%, <>50%, <>75%, and <100% of the order fulfilled based on the quoted volume. For example, Quote ID 2055F has a total quantity quoted of 80 units but these products were ordered at different times.
Product A was ordered first and of the 80 total units orders for that order ID, 20 units were ordered on Jan 5th, that is 25% of the quoted order was fulfilled. The number of days between order date and quoted date is 3 days.
Product C of that quote ID was ordered next with 15 units on Jan 18th. Now, the total units ordered is 35 or 44% of the quote. The number of days between order date and quoted date is 16 days.
On Feb 3rd, Product B was ordered with 5 units and the running total of the quote is now 50%. The number of days is 32 days.
Lastly, on Feb 8th, the remaining quoted units for Product B was ordered and now the running total of 50 (out of 80) or 62% of the total quoted volume. As
The result that I’d like to see is the following:
% of units ordered | Quote ID | Days between Quote & Order |
<=25% | 2055F | 3 days |
26%- 50% | 2055F | 16 days |
51% - 75% | 2055F | 32 days (not Feb 8 b/c it this order didn’t surpass 75%) |
<=100% | 2055F | n/a |
My problem is how to start to accumulate the % of units fulfilled based on the order date. Has this question been raised before? If so does someone know where I can find there answer? If not, does someone know how to perform this in Power BI/Dax?
Thank you!
