Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
The five tables we are working with are:
I've provided sample data for these tables below.
Some important notes:
** 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!
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:
And another in the Nonlinear Budget Table:
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.
(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 |
(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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |