Forum Discussion
Distribution calculation
Appologies for the confusion, I will make it clear.
I have mentioned 30 days here, but in actual that has to be replaced with a what if parameter so that it is flexible. Also the percentage (40%-60%) has to be replaced with what if parameter.That is the reason why I cant use a calculated table , and need a measue for the calculation.
I have a table with below value:
Month | Sum of Val |
9 | 1556 |
10 | 7317 |
11 | 1361 |
12 | 922 |
|
|
SEPTEMBER ( Value =1556)
1) 40% of that month value is distributed in the first 30 days.
So for Sep , 40% of 1556 is distributed from current day sept 18th to Oct 17.
40% of 1556=622.4
Days Remaining in Sep =13 days
Value for Sep = (622.4/30)*13 =269.7
2) Carryover for October from Sep 40% =
Days to carryover = (30-13)= 17 days
Value to be carried over to Oct = (622.4/30)*17 =352.69
3) 60% of 1556 is distributed Oct 18 to Nov 16.
60% of 1556 = 933.6
Value to be carried over to Oct = (933.6/30)*14 days in Oct =435.68
4) Value to be carried over to Nov =(933.6/30)*16 days =497.92
Total for Sep = 269.7
OCTOBER (VALUE = 7317)
1) 40% of 7317 is distributed from Oct 1- Oct 30
40% of 7317= 2926.8
Days in Oct =30 days = (2926.8/30) * 30days = 2926.8
2) 60% of 7317 = 4390.2
Has to be distributed from Oct 31 – Nov 29
So days in October = 1 days
= (4390.2/30)*1 = 146.34
3) Carryover for Nov from October 60%
Days in Nov = 29 days
Value = (4390.2/30)* 29= 4243.86
October Total = 2926.8+ 146.34+352.69(from sep 40%)+435.68(from Sep 60%)= 3861.51
Thank you for the walkthrough. I am working on the measure. Just for confirmation - if today were September 30, would you still want to distribute nearly the entire Sum of Val for September to october and november?
If today was Sep 30,
1) 40% of 1556=622.4
Days left in Sep = 1 day
Value for Sept = (622.4/30)* 1 day = 20.74
2)Carryover for Oct from Sep 40% = (Oct 1- Oct 29)= (622.4/30)*29 days= 601.65
3) 60% of 1556 will be distributed from Oct 30 onwards to Nov 28.
And then on Oct 1st that whole 1556 would disappear and no longer be distributed?
Yes, correct.This is forecasting logic.That value will get covered another measure.