Nonlinear Budget Release Measure

[Anonymous]

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:

1. Task Table - Tasks are in a hierarchy, this table identifies the hierarchy.
2. Budget Table - Where the budget hours live, by date (DateKey).
3. Dates - What the Budget Table connects to for dates.
4. Linearity Table - Table which dimensions whether the Task & Category are Linear or Nonlinear.
5. 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!

[v-shex-msft]

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

[Anonymous]

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])

RETURN

IF ( 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.

[Anonymous]

(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

[Anonymous]

(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