Forum Discussion
Combining Linear/Nonlinear Budgets
My current data assumes that every "Task" has a linear budget. Some of the tasks do not.
Below is an example of the tables I'm working with. The Budget Table gives the budget linearly, regardless of if the task has a linear budget or not. This cannot be changed. To work around that I'm hoping to create a measure that takes the total budget, whether that be SUM(Budget) or MAX(Budget YTD) and multiply it by the rolling sum of the Budget % from the Nonlinear Budget Table, if that task is nonlinear.
Current Data Model Relationships
I have tried calculated columns, measures and whatever else from different threads in this forum but none of them worked properly.
In the end I'd like to show the Linear/Nonlinear Budget YTD on a graph in one single line by date.
If more information is required, I'll respond as quickly as possible.
Thanks!
11 Replies
Is the nonlinear budget percentage valid for all the dates in that month? Not cumulative throughout the month?
The nonlinear budget percent is the % of the total budget for the whole year that gets released on that month. So technically yes, it's the same for every date in that month but I'd also like to see it smoothed so that it's cumulative throuhgout the month. Here's an example:
LinearityKey Date Budget % Budget % Cumulative Budget % Smoothed Total Year Budget Budget YTD Budget YTD Smoothed 1.2D 2022-07-04 0.25 0.25 0.008 99.000 24.75 0.80 1.2D 2022-07-05 0.25 0.25 0.016 99.000 24.75 1.60 1.2D 2022-07-06 0.25 0.25 0.024 99.000 24.75 2.40 1.2D … 0.25 0.25 … 99.000 24.75 … 1.2D 2022-07-15 0.25 0.25 0.13 99.000 24.75 12.38 1.2D … 0.25 0.25 … 99.000 24.75 … 1.2D 2022-07-29 0.25 0.25 0.25 99.000 24.75 24.75 1.2D … 0.25 0.25 … 99.000 24.75 … 1.2D 2022-08-01 0.5 0.75 0.267 99.000 74.25 26.40 1.2D … 0.5 0.75 … 99.000 74.25 … 1.2D 2022-08-15 0.5 0.75 0.5 99.000 74.25 49.50 1.2D … 0.5 0.75 … 99.000 74.25 … 1.2D 2022-08-31 0.5 0.75 0.75 99.000 74.25 74.25 1.2D 2022-09-01 0.25 1 0.758 99.000 99 75.08 1.2D … 0.25 1 … 99.000 99 … 1.2D 2022-09-15 0.25 1 0.875 99.000 99 86.63 1.2D … 0.25 1 … 99.000 99 … 1.2D 2022-09-30 0.25 1 1 99.000 99 99 I also just realized that my Nonlinear Budget Table is incorrect. The Budget %'s are not cumulative in the data. Please see the corrected version below.
Task Category First Day of Month 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 My last comment got deleted so my apologies if this gets posted twice.
Technically yes, the budget percentage is the percent of the total year budget that gets released in that month. Which is why I also would like to see it smoothed. The example below is what I'm looking for.
LinearityKey Date Budget % Budget % Cumulative Budget % Smoothed Total Year Budget Budget YTD Budget YTD Smoothed 1.2D 2022-07-04 0.25 0.25 0.009 99.000 24.75 0.85 1.2D 2022-07-05 0.25 0.25 0.017 99.000 24.75 1.71 1.2D 2022-07-06 0.25 0.25 0.026 99.000 24.75 2.56 1.2D … 0.25 0.25 … 99.000 24.75 … 1.2D 2022-07-15 0.25 0.25 0.13 99.000 24.75 12.38 1.2D … 0.25 0.25 … 99.000 24.75 … 1.2D 2022-07-29 0.25 0.25 0.25 99.000 24.75 24.75 1.2D 2022-08-01 0.5 0.75 0.267 99.000 74.25 26.40 1.2D … 0.5 0.75 … 99.000 74.25 … 1.2D 2022-08-15 0.5 0.75 0.5 99.000 74.25 49.50 1.2D … 0.5 0.75 … 99.000 74.25 … 1.2D 2022-08-31 0.5 0.75 0.75 99.000 74.25 74.25 1.2D 2022-09-01 0.25 1 0.758 99.000 99 75.08 1.2D … 0.25 1 … 99.000 99 … 1.2D 2022-09-15 0.25 1 0.875 99.000 99 86.63 1.2D … 0.25 1 … 99.000 99 … 1.2D 2022-09-30 0.25 1 1 99.000 99 99 I also just realized my Nonlinear Budget Table had cumulative percentages, but in my data they aren't cumulative. I've edited the original comment and I'll provide the revised version below as well.
Nonlinear Budget Table
Task Category First Day of Month 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
That should be doable - maybe even by combining both budget tables into one.
Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Task 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 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 Nonlinear Budget Table
Task Category First Day of Month DateKey Budget % 1.2 D 2022-07-04 20220701 0.25 1.2 D 2022-08-01 20220801 0.5 1.2 D 2022-09-01 20220801 0.25 1.3.2 B 2022-07-04 20220701 0.125 1.3.2 B 2022-08-01 20220801 0.125 1.3.2 B 2022-09-01 20220801 0.75 3.1.2 A 2022-07-04 20220701 0.7 3.1.2 A 2022-08-01 20220801 0 3.1.2 A 2022-09-01 20220801 0.3 Dates
Date DateKey Day Month Year 2022-07-04 20220704 4 7 2022 2022-07-15 20220715 15 7 2022 2022-07-29 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 Note: In the Budget Table and Dates table there is usually every work day of the month, I summarized by putting the first work day, the 15th and the last work day.
To not exceed max characters, I'll respond with the Budget Table and what I'm looking for in the next comment(s).
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 This is a photo of what I'm looking for that I did quickly in Excel. I'd like to be able to have measures for the Budget YTD and Budget YTD Smoothed. Looking for the ability to filter it by Category as well.
