topic Re: Combining Linear/Nonlinear Budgets in DAX Commands and Tips

Combining Linear/Nonlinear Budgets

Anonymous — Fri, 26 Aug 2022 15:03:02 GMT

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.

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!

Re: Combining Linear/Nonlinear Budgets

lbendlin — Sat, 27 Aug 2022 21:43:18 GMT

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/1447523

Re: Combining Linear/Nonlinear Budgets

Anonymous — Tue, 30 Aug 2022 11:10:48 GMT

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

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

Re: Combining Linear/Nonlinear Budgets

Anonymous — Mon, 29 Aug 2022 12:04:15 GMT

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.

Re: Combining Linear/Nonlinear Budgets

Anonymous — Mon, 29 Aug 2022 12:06:21 GMT

I've provided the additional information in the replies. I am pulling the Budget Table data from Analysis Services and the Nonlinear Budget Table from an Excel file.

Re: Combining Linear/Nonlinear Budgets

lbendlin — Mon, 29 Aug 2022 20:50:12 GMT

Is the nonlinear budget percentage valid for all the dates in that month? Not cumulative throughout the month?

Re: Combining Linear/Nonlinear Budgets

Anonymous — Tue, 30 Aug 2022 11:17:41 GMT

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

Re: Combining Linear/Nonlinear Budgets

Anonymous — Tue, 30 Aug 2022 11:26:50 GMT

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

Re: Combining Linear/Nonlinear Budgets

Anonymous — Tue, 30 Aug 2022 11:28:41 GMT

I accidentally replied to your last comment with my response.

Re: Combining Linear/Nonlinear Budgets

Anonymous — Tue, 30 Aug 2022 12:15:54 GMT

I'm not sure why my comment keeps getting deleted. But yes it technically is the budget percentage for the entire month. I'd also like to see it smoothed. I accidentally showed my budget % in a cumulative way, which isn't the case in the data. I've edited the Nonlinear Budget Table in the comment accordingly.

I'm looking for both the Budget YTD and the Budget YTD Smoothed. Here is an example:

Re: Combining Linear/Nonlinear Budgets

lbendlin — Fri, 02 Sep 2022 12:47:41 GMT

Still trying to understand all the mechanics - budgeting is not my forte. If you still need assistance could we maybe start over with a fresh premise so I can ignore all the above changes?

Re: Combining Linear/Nonlinear Budgets

Anonymous — Fri, 02 Sep 2022 14:15:44 GMT

Yes my apologies, somehow all 3 comments got posted after some time passed so it got confusing.

Essentially some tasks are linear, and some are nonlinear. The current data assumes all are linear and does a calculation like so:

Budget = ( [DaysInQuestion] / [DaysInYear] ) * [TotalBudget]

Budget YTD = ( [DaysInQuestionYTD] / [DaysInYear] ) * [TotalBudget]

*Please note that these are imported from Analysis Services so they are static values in the report I am using.

However some tasks don't have linear budgets, which is defined in the 'Linearity Table'. These nonlinear budgets are released in percentages. So for example, the budget for task 1.3.2 only recieves 12.5% of its total budget in July, another 12.5% more of its total budget in August and 75% more of its total budget in September. This means that by September, the task will have access to the full 100% of its total budget ( 12.5% + 12.5% + 75% = 100% ). These values are found in the 'Nonlinear Budget Table'.

I want to be able to see the curve for the nonlinear budgets YTD like the image below. Both smoothed so that the percentage is gradual throughout the month, and with the whole percentage being released in the first day.