Forum Discussion
Distribution calculation
40% Distribution (First 30 Days):
The first portion (40%) of the value for the current month should be distributed over 30 days, but only the days remaining in the current month should be considered. The remaining days (if less than 30) should carry over to the next month.
The term "remaining" is used ambiguously. Please provide concrete examples for the September and October numbers.
Why 30 days? Why not one month? Just to make the calculation more difficult?
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.