Forum Discussion
Distribution calculation
I want to calculate a measure which:
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.
60% Distribution (Next 30 Days):
- After distributing 40% over the first 30 days, the remaining 60% will be distributed in the next 30-day period.
- This will include any leftover days from the previous 40% distribution that couldn’t be allocated fully within the current month.
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
I have created this calculated table which works as expected , but i need it as a measure so that i can accomodate what if parameters for distribution percentage and distributon length:
DAX for cal table:
8 Replies
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?