Forum Discussion
Nonlinear Budget Release Measure
Hi,
I posted this question a little over a week ago but I don't think I properly articulated what I was looking for. This is the Link.
The current data assumes that every [Task] has a linear budget in the [Budget] and [Budget YTD] fields.
[Budget] = ( [DaysInDateContext] / [DaysInYear] ) * [TotalBudgetHours]
[Budget YTD] = ( [DaysInDateContextYTD] / [DaysInYear] ) * [TotalBudgetHours]
*Note: These are imported static values, not measures. These calculations are done prior to importing.
However, some Tasks have nonlinear budget releases.
An example of this would be: Task X has a total yearly budget of 1,000 hours. Task X receives 12.5% of its budget in the month of July, an additional 12.5% in August, and 75% in September. This means that in January through June, the task has 0 budget hours released. In July it has 125 budget hours, and another 125 budget hours released in August for a total of 250 budget hours. September comes and 750 budget hours is released to that task, for a total of 1,000 hours.
Here is a sample of the model I am working with.
Model Relationships
The five tables we are working with are:
- Task Table - Tasks are in a hierarchy, this table identifies the hierarchy.
- Budget Table - Where the budget hours live, by date (DateKey).
- Dates - What the Budget Table connects to for dates.
- Linearity Table - Table which dimensions whether the Task & Category are Linear or Nonlinear.
- Nonlinear Budget Table - Where the nonlinear budget release percentages live.
I've provided sample data for these tables below.
Some important notes:
- Linearity depends on both the task and the task category. So a task may be linear in one category budget, but nonlinear in another.
- The budgets are actually every working day, to simplify it I just used the first, middle and last working day of each month.
- The nonlinear budget releases are released on a monthly basis. This means there should be a big jump when plotting them by day.
** TO NOT EXCEED CHARACTER COUNT, THE TABLES WILL BE IN REPLIES BELOW **
This is my current DAX measure..
Nonlinear Budget YTD =
VAR BudgetHours =
CALCULATE (
SUM ( 'Budget Table'[Budget] ),
'Linearity'[Linearity] = "Nonlinear"
)
VAR LastTableDate =
MAX('Nonlinear Budget'[FirstWorkdayOfMonth])
VAR Budget%YTD =
CALCULATE (
SUM ( 'Nonlinear Budget'[Budget%]),
ALLEXCEPT ( 'Nonlinear Budget',
'Nonlinear Budget'[Category],
'Nonlinear Budget'[Task]
),
'Nonlinear Budget'[FirstWorkdayOfMonth] <= LastTableDate
)
RETURN
Budget%YTD * BudgetHours
The issue I'm running into is that when I plot it against the date, it's taking the sum of all of the Task & Categories Budget% for that month and muliplying it by the total budget hours. I don't know how to make it calculate every Task & Category separately and then sum those values up.
Example:
What I'm mainly lookign for is to be able to plot all budgets (nonlinear and linear) on a single graph by date. As shown below.
Bonus if we can smooth it so that the full nonlinear budget % gets released gradually throughout the month, and not just on the first day like so:
If any additional information is required, I'll respond as quick as I can.
Thank you!
4 Replies
(1/5) Task Table
Task Task Level 1 Task Level 2 Task Level 3 1.2 1 1.2 1.3.2 1 1.3 1.3.2 2.1.2 2 2.1 2.1.2 2.2.3 2 2.2 2.2.3 2.3 2 2.3 3.1.2 3 3.1 3.1.2 (2/5) Budget Table
Task Category DateKey Budget Budget YTD LinearityKey 1.2 A 20220704 10 10 1.2A 1.2 A 20220715 10 20 1.2A 1.2 A 20220729 10 30 1.2A 1.2 A 20220801 10 40 1.2A 1.2 A 20220815 10 50 1.2A 1.2 A 20220831 10 60 1.2A 1.2 A 20220901 10 70 1.2A 1.2 A 20220915 10 80 1.2A 1.2 A 20220930 10 90 1.2A 1.2 D 20220704 11 11 1.2D 1.2 D 20220715 11 22 1.2D 1.2 D 20220729 11 33 1.2D 1.2 D 20220801 11 44 1.2D 1.2 D 20220815 11 55 1.2D 1.2 D 20220831 11 66 1.2D 1.2 D 20220901 11 77 1.2D 1.2 D 20220915 11 88 1.2D 1.2 D 20220930 11 99 1.2D 1.3.2 B 20220704 5 5 1.3.2B 1.3.2 B 20220715 5 10 1.3.2B 1.3.2 B 20220729 5 15 1.3.2B 1.3.2 B 20220801 5 20 1.3.2B 1.3.2 B 20220815 5 25 1.3.2B 1.3.2 B 20220831 5 30 1.3.2B 1.3.2 B 20220901 5 35 1.3.2B 1.3.2 B 20220915 5 40 1.3.2B 1.3.2 B 20220930 5 45 1.3.2B 2.1.2 C 20220704 7 7 2.1.2C 2.1.2 C 20220715 7 14 2.1.2C 2.1.2 C 20220729 7 21 2.1.2C 2.1.2 C 20220801 7 28 2.1.2C 2.1.2 C 20220815 7 35 2.1.2C 2.1.2 C 20220831 7 42 2.1.2C 2.1.2 C 20220901 7 49 2.1.2C 2.1.2 C 20220915 7 56 2.1.2C 2.1.2 C 20220930 7 63 2.1.2C 2.2.3 A 20220704 4 4 2.2.3A 2.2.3 A 20220715 4 8 2.2.3A 2.2.3 A 20220729 4 12 2.2.3A 2.2.3 A 20220801 4 16 2.2.3A 2.2.3 A 20220815 4 20 2.2.3A 2.2.3 A 20220831 4 24 2.2.3A 2.2.3 A 20220901 4 28 2.2.3A 2.2.3 A 20220915 4 32 2.2.3A 2.2.3 A 20220930 4 36 2.2.3A 2.3 D 20220704 4 4 2.3D 2.3 D 20220715 4 8 2.3D 2.3 D 20220729 4 12 2.3D 2.3 D 20220801 4 16 2.3D 2.3 D 20220815 4 20 2.3D 2.3 D 20220831 4 24 2.3D 2.3 D 20220901 4 28 2.3D 2.3 D 20220915 4 32 2.3D 2.3 D 20220930 4 36 2.3D 3.1.2 A 20220704 1 1 3.1.2A 3.1.2 A 20220715 1 2 3.1.2A 3.1.2 A 20220729 1 3 3.1.2A 3.1.2 A 20220801 1 4 3.1.2A 3.1.2 A 20220815 1 5 3.1.2A 3.1.2 A 20220831 1 6 3.1.2A 3.1.2 A 20220901 1 7 3.1.2A 3.1.2 A 20220915 1 8 3.1.2A 3.1.2 A 20220930 1 9 3.1.2A (3/5) Dates
Date DateKey Day Month Year 2022-07-04 20220704 4 7 2022 2022-07-15 20220715 15 7 2022 2022-07-31 20220729 29 7 2022 2022-08-01 20220801 1 8 2022 2022-08-15 20220815 15 8 2022 2022-08-31 20220831 31 8 2022 2022-09-01 20220901 1 9 2022 2022-09-15 20220915 15 9 2022 2022-09-30 20220930 31 9 2022 (4/5) Linearity Table
Task Category Linearity LinearityKey 1.2 A Linear 1.2A 1.2 D Nonlinear 1.2D 1.3.2 B Nonlinear 1.3.2B 2.1.2 C Linear 2.1.2C 2.2.3 A Linear 2.2.3A 2.3 D Linear 2.3D 3.1.2 A Nonlinear 3.1.2A (5/5) Nonlinear Budget Table
Task Category FirstWorkdayofMonth DateKey Budget % 1.2 D 2022-07-04 20220704 0.25 1.2 D 2022-08-01 20220801 0.5 1.2 D 2022-09-01 20220901 0.25 1.3.2 B 2022-07-04 20220704 0.125 1.3.2 B 2022-08-01 20220801 0.125 1.3.2 B 2022-09-01 20220901 0.75 3.1.2 A 2022-07-04 20220704 0.7 3.1.2 A 2022-08-01 20220801 0 3.1.2 A 2022-09-01 20220901 0.3 Hi Anonymous,
It seems like a common measure total level calculations issue, you can take a look at the following blog to know how to use measure expression to calculate with multiple aggregations:
Measure Totals, The Final Word
Regards,Xiaoxin Sheng
Thank you, that blog helped a lot.
I ended up changing the model relationships by adding a Nonlinear Budget Key to both the Budget Table and Nonlinear Budget Table, which was done by concatenating the Task, Category and Date fields.
Additionally, I created a calculated column in the Budget Table like so:
Max Budget Hours =CALCULATE ( MAX ( 'Budget Table'[Budget YTD] ),ALLEXCEPT ('Budget Table','Budget Table'[Task],'Budget Table'[Category] )
)And another in the Nonlinear Budget Table:
Running Total =CALCULATE (SUM ( 'Nonlinear Budget Table'[Value]),ALLEXCEPT ( 'Nonlinear Budget Table','Nonlinear Budget Table'[Category],'Nonlinear Budget Table'[Task] ),'Nonlinear Budget Table'[Date] <= EARLIER('Nonlinear Budget Table'[Date] ))Which I then used the link you provided to make these measures:NonlinearBudgetBase = SUM ('Budget Table'[Max Budget Hours]) *SUM ('Nonlinear Budget Table'[Running Total])NonlinearBudgetbyDate =VAR _table = SUMMARIZE('Task Table', 'Task Table'[Task], "_value", [NonlinearBudgetBase])RETURNIF ( HASONEVALUE ( 'Task Table' [Task]), [NonlinearBudgetBase], SUMX(_table, [_value]))I'm still struggling with getting it to repeat for every day in that month. What I'm getting looks like this when it is summed with the linear budget, since that is daily, whereas since the Nonlinear Budget % is only on the first day of each month.
Please let me know if you have any suggestions to solve this problem.