topic Re: Distribution calculation in DAX Commands and Tips

Distribution calculation

Anonymous — Wed, 18 Sep 2024 13:48:46 GMT

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:

Distributions Alrernate =

VAR StartDate = TODAY()

VAR EndDate = StartDate + 365 // Adjust as needed to cover all distribution periods

VAR DatesTable =

SELECTCOLUMNS(

CALENDAR(StartDate, EndDate),

"DistributionDate", [Date]

)

RETURN

GENERATE(

'Sheet1',

VAR MonthValue = 'Sheet1'[Value]

VAR MonthNumber = 'Sheet1'[MonthNo]

VAR MonthStartDate = DATE(YEAR(StartDate), MonthNumber, 1)

VAR FirstDistributionStart =

IF(

MonthStartDate < StartDate && MONTH(StartDate) = MonthNumber,

StartDate, // For the current month, start from today if today is after the month's start

MonthStartDate // For future months, start from the first day of the month

)

VAR FirstDistributionEnd = FirstDistributionStart + 29 // First 30-day period

// Define the Second Distribution Start Date

VAR SecondDistributionStart = FirstDistributionEnd + 1

VAR SecondDistributionEnd = SecondDistributionStart + 29 // Second 30-day period

// Create daily records for distribution periods

VAR DailyRecords =

FILTER(

DatesTable,

([DistributionDate] >= FirstDistributionStart && [DistributionDate] <= FirstDistributionEnd) ||

([DistributionDate] >= SecondDistributionStart && [DistributionDate] <= SecondDistributionEnd)

)

RETURN

ADDCOLUMNS(

DailyRecords,

"DistributionAmount 40%",IF(([DistributionDate] >= FirstDistributionStart && [DistributionDate] <= FirstDistributionEnd),(0.4 * MonthValue) / 30,0),

"DistributionAmount 60%",IF(([DistributionDate] >= SecondDistributionStart && [DistributionDate] <= SecondDistributionEnd),(0.6 * MonthValue) / 30,0)

)

Re: Distribution calculation

lbendlin — Wed, 18 Sep 2024 11:56:32 GMT

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?

Re: Distribution calculation

Anonymous — Wed, 18 Sep 2024 13:55:33 GMT

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

Re: Distribution calculation

lbendlin — Wed, 18 Sep 2024 14:47:38 GMT

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?

Re: Distribution calculation

Anonymous — Wed, 18 Sep 2024 15:00:19 GMT

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.

Re: Distribution calculation

lbendlin — Wed, 18 Sep 2024 15:26:54 GMT

And then on Oct 1st that whole 1556 would disappear and no longer be distributed?

Re: Distribution calculation

Anonymous — Wed, 18 Sep 2024 17:33:04 GMT

Yes, correct.This is forecasting logic.That value will get covered another measure.

Re: Distribution calculation

lbendlin — Wed, 18 Sep 2024 17:46:37 GMT

Re: Distribution calculation

Anonymous — Wed, 18 Sep 2024 18:37:21 GMT

Beautiful!!