Accumulating % totals

Cali_2020 — Fri, 19 Apr 2024 12:45:09 GMT

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!

Re: Accumulating % totals

lbendlin — Sat, 20 Apr 2024 19:08:08 GMT

How are you planning to forecast, based on which method? Working days only? Holidays? For linear regression you can use LINESTX.

Have you considered using a graphical solution?

topic Re: Accumulating % totals in DAX Commands and Tips

Accumulating % totals

Re: Accumulating % totals