Try to create an estimate of a value per day

[Anonymous]

Hi All! Apologies for the vague title and length of this post but I'm trying to achieve something quite tricky and not sure how to exactly tackle it.

I am trying to create an estimate for faults that occur based on previous ratios. In the table below, if we assume a factory with two different types of machines, a daily Production figure that is always available, Unscheduled Deferment when something unexpectedly causes production to drop (e.g. a machine gets stuck or breaks down), and Scheduled Deferment (e.g. routine maintenance), and a Theoretical Prod which is the sum of Production+Unscheduled Deferment+Scheduled Deferment.

Date	Unscheduled Deferment	Scheduled Deferment	Theoretical Prod	Production	Machine Type
31/12/2019	24	5	57	28	A
31/12/2019	12	1	42	29	B
01/01/2020	23	2	55	30	A
01/01/2020	12	5	45	28	B
02/01/2020	1	2	46	43	B
02/01/2020	23	9	69	37	A
03/01/2020			12	12	A
03/01/2020			40	40	B
04/01/2020			23	23	B
04/01/2020			11	11	A

My initial logic is as follows, assuming we've selected a slicer where only Machine Type "A" is selected:

1. Find the dates where both the Unscheduled Deferment & Scheduled Deferment columns are BLANK() (i.e. 03/01/2020, 04/01/2020).
2. Then, assuming we are using the past two days to draw our estimates from (i.e. 01/01/2020, 02/01/2020) I would like to get the average Theoretical Production: (30+37)/2 = 33.5 
3. Then, I would like to do the same for the Unscheduled/Scheduled Ratio to allow me to distribute the change in production to Unscheduled & Scheduled deferments in the same ratio as shown in the past two days. So: (23+23)/(23+23+9+2) = 0.807
4. Now, I would use the average Theoretical Production from Step 2. and the daily Production figure to estimate the deferments. For 03/01/2020, I would do: 33.5-12 = 21.5  So 21.5 is the difference in Production and essentially what we expect to be theoretically producing. The difference being due to Unscheduled & Scheduled Deferment. Using the previous ratio, I would estimate Unscheduled Deferment to be 21.5 * 0.807 = 17.35 and Scheduled Deferment to be 21.5 - 17.35 = 4.15
5. I would then repeat the same for 04/01/2020

In theory, I would end up with a table similar to the one below which would allow me to visualise on a daily basis the estimates:

Date	Unscheduled	Scheduled	Theoretical Prod	Production	Machine Type
31/12/2019	24	5	57	28	A
31/12/2019	12	1	42	29	B
01/01/2020	23	2	55	30	A
01/01/2020	12	5	45	28	B
02/01/2020	1	2	46	43	B
02/01/2020	23	9	69	37	A
03/01/2020	17.35	4.15	33.5	12	A
03/01/2020	3.58	1.92	45.5	40	B
04/01/2020	14.63	7.87	45.5	23	B
04/01/2020	18.16	4.34	33.5	11	A

Any help or pointers in how to achieve this steps would be much appreciated as I am quite lost! I am able to find the last day that contains deferment data but then I am getting confused using calculated columns and measures.

[lbendlin]

you will want to consider statistical significance.  In other words - you don't have enough data points for reliable predictions.

Once you have (many) more data points  you can then simplify your formulas and treat both unscheduled and maintenance as regular parts of your data.

[Anonymous]

Hi @lbendlin, I understand it's not the soundest of methods of generating estimates but it was a method accepted by the business.