cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ct2289
Helper I
Helper I

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 RelationshipsModel 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:

PBI Help 5.PNG

 

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.

PBI Help 6.PNG

 

 

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:

 

PBI Help 7.PNG

 

If any additional information is required, I'll respond as quick as I can.

 

Thank you!

 

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @ct2289,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

 

PBI Help 9.PNG

 

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
IFHASONEVALUE '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.

PBI Help 8.PNG

 

Please let me know if you have any suggestions to solve this problem.

ct2289
Helper I
Helper I

(3/5) Dates

DateDateKeyDayMonthYear
2022-07-0420220704472022
2022-07-15202207151572022
2022-07-31202207292972022
2022-08-0120220801182022
2022-08-15202208151582022
2022-08-31202208313182022
2022-09-0120220901192022
2022-09-15202209151592022
2022-09-30202209303192022

 

(4/5) Linearity Table

TaskCategoryLinearityLinearityKey
1.2ALinear1.2A
1.2DNonlinear1.2D
1.3.2BNonlinear1.3.2B
2.1.2CLinear2.1.2C
2.2.3ALinear2.2.3A
2.3DLinear2.3D
3.1.2ANonlinear3.1.2A

 

(5/5) Nonlinear Budget Table

TaskCategoryFirstWorkdayofMonthDateKeyBudget %
1.2D2022-07-04202207040.25
1.2D2022-08-01202208010.5
1.2D2022-09-01202209010.25
1.3.2B2022-07-04202207040.125
1.3.2B2022-08-01202208010.125
1.3.2B2022-09-01202209010.75
3.1.2A2022-07-04202207040.7
3.1.2A2022-08-01202208010
3.1.2A2022-09-01202209010.3
ct2289
Helper I
Helper I

(1/5) Task Table

TaskTask Level 1Task Level 2Task Level 3
1.211.2 
1.3.211.31.3.2
2.1.222.12.1.2
2.2.322.22.2.3
2.322.3 
3.1.233.13.1.2

 

 

(2/5) Budget Table

TaskCategoryDateKeyBudget Budget YTDLinearityKey
1.2A2022070410101.2A
1.2A2022071510201.2A
1.2A2022072910301.2A
1.2A2022080110401.2A
1.2A2022081510501.2A
1.2A2022083110601.2A
1.2A2022090110701.2A
1.2A2022091510801.2A
1.2A2022093010901.2A
1.2D2022070411111.2D
1.2D2022071511221.2D
1.2D2022072911331.2D
1.2D2022080111441.2D
1.2D2022081511551.2D
1.2D2022083111661.2D
1.2D2022090111771.2D
1.2D2022091511881.2D
1.2D2022093011991.2D
1.3.2B20220704551.3.2B
1.3.2B202207155101.3.2B
1.3.2B202207295151.3.2B
1.3.2B202208015201.3.2B
1.3.2B202208155251.3.2B
1.3.2B202208315301.3.2B
1.3.2B202209015351.3.2B
1.3.2B202209155401.3.2B
1.3.2B202209305451.3.2B
2.1.2C20220704772.1.2C
2.1.2C202207157142.1.2C
2.1.2C202207297212.1.2C
2.1.2C202208017282.1.2C
2.1.2C202208157352.1.2C
2.1.2C202208317422.1.2C
2.1.2C202209017492.1.2C
2.1.2C202209157562.1.2C
2.1.2C202209307632.1.2C
2.2.3A20220704442.2.3A
2.2.3A20220715482.2.3A
2.2.3A202207294122.2.3A
2.2.3A202208014162.2.3A
2.2.3A202208154202.2.3A
2.2.3A202208314242.2.3A
2.2.3A202209014282.2.3A
2.2.3A202209154322.2.3A
2.2.3A202209304362.2.3A
2.3D20220704442.3D
2.3D20220715482.3D
2.3D202207294122.3D
2.3D202208014162.3D
2.3D202208154202.3D
2.3D202208314242.3D
2.3D202209014282.3D
2.3D202209154322.3D
2.3D202209304362.3D
3.1.2A20220704113.1.2A
3.1.2A20220715123.1.2A
3.1.2A20220729133.1.2A
3.1.2A20220801143.1.2A
3.1.2A20220815153.1.2A
3.1.2A20220831163.1.2A
3.1.2A20220901173.1.2A
3.1.2A20220915183.1.2A
3.1.2A20220930193.1.2A

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors