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 want to allocate surplus or deficit budget back into the same account and sub-account. The goal is to allocate the remaining budget back into the account and sub-account on a monthly level, depending on what proportion of our annual budget we planned to spend that month.
E.g.1 - We have an annual budget of $100 and $10 of that budget was allocated to Account X in November, therefore, we planned to spend 10% of our budget on Account X in November. If after month 1, we have a surplus of $1 for that account, 10% of it must be assigned to November. The same goes for a deficit amount, we should reduce the original spend in November by 10% for that account.
E.g. Tables - we have 2 accounts and within these accounts, we have sub-accounts.
There are two fact tables (1) Monthly Budget per Account & Sub-Account (2) a Cost/ Spend table with invoice entries per Account & Sub-Account.
----- Month Budget Table: -----
Month & Year | Account | Sub-Account | Budget |
Jul-21 | Direct Marketing | AdWords | $20 000,00 |
Jul-21 | Direct Marketing | $15 000,00 | |
Aug-21 | Direct Marketing | AdWords | $25 000,00 |
Aug-21 | Direct Marketing | $17 000,00 | |
Sep-21 | Direct Marketing | AdWords | $27 000,00 |
Sep-21 | Direct Marketing | $15 000,00 | |
Oct-21 | Direct Marketing | AdWords | $20 000,00 |
Oct-21 | Direct Marketing | $20 000,00 | |
Nov-21 | Direct Marketing | AdWords | $30 000,00 |
Nov-21 | Direct Marketing | $20 000,00 | |
Dec-21 | Direct Marketing | AdWords | $24 000,00 |
Dec-21 | Direct Marketing | $20 000,00 | |
Jan-22 | Direct Marketing | AdWords | $15 000,00 |
Jan-22 | Direct Marketing | $15 000,00 | |
Jul-21 | TV | Ad Creation | $17 000,00 |
Jul-21 | TV | Sponsorships | $8 000,00 |
Jul-21 | TV | Content | $18 000,00 |
Aug-21 | TV | Ad Creation | $10 000,00 |
Aug-21 | TV | Sponsorships | $18 000,00 |
Aug-21 | TV | Content | $0,00 |
Sep-21 | TV | Ad Creation | $25 000,00 |
Sep-21 | TV | Sponsorships | $15 000,00 |
Sep-21 | TV | Content | $19 000,00 |
Oct-21 | TV | Ad Creation | $0,00 |
Oct-21 | TV | Sponsorships | $12 000,00 |
Oct-21 | TV | Content | $23 000,00 |
Nov-21 | TV | Ad Creation | $15 000,00 |
Nov-21 | TV | Sponsorships | $15 000,00 |
Nov-21 | TV | Content | $19 000,00 |
Dec-21 | TV | Ad Creation | $0,00 |
Dec-21 | TV | Sponsorships | $25 000,00 |
Dec-21 | TV | Content | $0,00 |
Jan-22 | TV | Ad Creation | $23 000,00 |
Jan-22 | TV | Sponsorships | $0,00 |
Jan-22 | TV | Content | $15 000,00 |
----- Cost/ Spend Table:-----
Date | Month | Account | Sub-Account | Invoice # | Etc. | Cost (Incl. VAT) |
01-Jul | Jul-21 | Direct Marketing | AdWords | 123 | … | $4 700,00 |
02-Jul | Jul-21 | Direct Marketing | 124 | … | $3 200,00 | |
03-Jul | Jul-21 | TV | Ad Creation | 125 | … | $2 500,00 |
04-Jul | Jul-21 | Direct Marketing | 126 | … | $3 000,00 | |
05-Jul | Jul-21 | TV | Sponsorships | 127 | … | $3 200,00 |
06-Jul | Jul-21 | TV | Content | 128 | … | $5 750,00 |
07-Jul | Jul-21 | TV | Ad Creation | 129 | … | $2 700,00 |
08-Jul | Jul-21 | Direct Marketing | AdWords | 130 | … | $4 900,00 |
09-Jul | Jul-21 | Direct Marketing | 131 | … | $2 100,00 | |
10-Jul | Jul-21 | TV | Ad Creation | 132 | … | $2 400,00 |
11-Jul | Jul-21 | TV | Content | 133 | … | $3 000,00 |
12-Jul | Jul-21 | TV | Sponsorships | 134 | … | $4 000,00 |
13-Jul | Jul-21 | Direct Marketing | AdWords | 135 | … | $5 100,00 |
14-Jul | Jul-21 | TV | Content | 136 | … | $6 100,00 |
15-Jul | Jul-21 | TV | Ad Creation | 137 | … | $2 500,00 |
16-Jul | Jul-21 | Direct Marketing | AdWords | 138 | … | $4 750,00 |
17-Jul | Jul-21 | TV | Ad Creation | 139 | … | $2 500,00 |
18-Jul | Jul-21 | TV | Sponsorships | 140 | … | $2 900,00 |
19-Jul | Jul-21 | Direct Marketing | 141 | … | $1 900,00 | |
20-Jul | Jul-21 | Direct Marketing | 142 | … | $3 100,00 | |
21-Jul | Jul-21 | TV | Ad Creation | 143 | … | $2 600,00 |
22-Jul | Jul-21 | TV | Ad Creation | 144 | … | $2 490,00 |
In addition to these tables I have invoked a calendar table in power query, fiscal year starting at 7. Potentially, some useful fields in the calendar table may include columns that show if the day, month, year etc. is completed or not {True} / {False}
I also have dim tables for Account and Sub-Account.
I have managed to re-allocate the budget on a monthly level, but not back into the same account where the surplus/ deficit comes from. No point in give Account A surplus/ deficit budget from Account B, we'd want to mange Account B accordingly.
Hope this is enough info
Cheers
Solved! Go to Solution.
Hi, @Stones
Try formula as below:
New Budget Mix =
VAR value1 =
CALCULATE (
SUM ( 'ALLOCATED BUDGET'[Monthly Allocated Budget] ),
FILTER (
ALL ( 'ALLOCATED BUDGET' ),
'ALLOCATED BUDGET'[Sub-Account] = MAX ( 'ALLOCATED BUDGET'[Sub-Account] )
&& 'ALLOCATED BUDGET'[Month & Year]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ) //Here is 2021,Auguest,1. The date here needs to be adjusted by yourself
)
)
VAR value2 =
IF (
MAX ( 'ALLOCATED BUDGET'[Month & Year] )
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ),
MAX ( 'ALLOCATED BUDGET'[Monthly Allocated Budget] ),
BLANK ()
)
RETURN
value2 / value1
YTD Cost (Incl. VAT) =
CALCULATE (
SUM ( SPEND[Cost (Incl. VAT)] ),
USERELATIONSHIP ( SPEND[Sub-Account], 'ALLOCATED BUDGET'[Sub-Account] )
)
1_Surplus/ Deficit =
CALCULATE (
SUM ( 'ALLOCATED BUDGET'[Monthly Allocated Budget] ) - [YTD Cost (Incl. VAT)],
FILTER (
ALLEXCEPT ( 'ALLOCATED BUDGET', 'ALLOCATED BUDGET'[Sub-Account] ),
'ALLOCATED BUDGET'[Month & Year].[MonthNo] = MONTH ( TODAY () )
)
)
New/ Revised Budget =
VAR value1 =
[New Budget Mix] * [1_Surplus/ Deficit]
+ SUM ( 'ALLOCATED BUDGET'[Monthly Allocated Budget] )
RETURN
IF (
MAX ( 'ALLOCATED BUDGET'[Month & Year] )
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ),
value1,
BLANK ()
)
Result:
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, this resolved my issue
Hi, @Stones
Not fully sure what is your question, can you elaborate a bit more.
Please provide more details to explain what you are trying to do.
Expected result in excel will better help us understand your question.
Best Regards,
Community Support Team _ Eason
Hi
Here is a onedrive link to the excel file.
https://1drv.ms/x/s!ArrCtydCcqFgglcHcUWTEGrLL3Yd?e=Kzdhbd
I have added notes in the excel file which will hopefully help add the outcome I'm looking for.
Thank you
Hi, @Stones
Try formula as below:
New Budget Mix =
VAR value1 =
CALCULATE (
SUM ( 'ALLOCATED BUDGET'[Monthly Allocated Budget] ),
FILTER (
ALL ( 'ALLOCATED BUDGET' ),
'ALLOCATED BUDGET'[Sub-Account] = MAX ( 'ALLOCATED BUDGET'[Sub-Account] )
&& 'ALLOCATED BUDGET'[Month & Year]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ) //Here is 2021,Auguest,1. The date here needs to be adjusted by yourself
)
)
VAR value2 =
IF (
MAX ( 'ALLOCATED BUDGET'[Month & Year] )
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ),
MAX ( 'ALLOCATED BUDGET'[Monthly Allocated Budget] ),
BLANK ()
)
RETURN
value2 / value1
YTD Cost (Incl. VAT) =
CALCULATE (
SUM ( SPEND[Cost (Incl. VAT)] ),
USERELATIONSHIP ( SPEND[Sub-Account], 'ALLOCATED BUDGET'[Sub-Account] )
)
1_Surplus/ Deficit =
CALCULATE (
SUM ( 'ALLOCATED BUDGET'[Monthly Allocated Budget] ) - [YTD Cost (Incl. VAT)],
FILTER (
ALLEXCEPT ( 'ALLOCATED BUDGET', 'ALLOCATED BUDGET'[Sub-Account] ),
'ALLOCATED BUDGET'[Month & Year].[MonthNo] = MONTH ( TODAY () )
)
)
New/ Revised Budget =
VAR value1 =
[New Budget Mix] * [1_Surplus/ Deficit]
+ SUM ( 'ALLOCATED BUDGET'[Monthly Allocated Budget] )
RETURN
IF (
MAX ( 'ALLOCATED BUDGET'[Month & Year] )
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ),
value1,
BLANK ()
)
Result:
Please check my sample file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |