Allocating Surplus/ Deficit Budget to Correct Account/ Category

[Stones]

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	Facebook	$15 000,00
Aug-21	Direct Marketing	AdWords	$25 000,00
Aug-21	Direct Marketing	Facebook	$17 000,00
Sep-21	Direct Marketing	AdWords	$27 000,00
Sep-21	Direct Marketing	Facebook	$15 000,00
Oct-21	Direct Marketing	AdWords	$20 000,00
Oct-21	Direct Marketing	Facebook	$20 000,00
Nov-21	Direct Marketing	AdWords	$30 000,00
Nov-21	Direct Marketing	Facebook	$20 000,00
Dec-21	Direct Marketing	AdWords	$24 000,00
Dec-21	Direct Marketing	Facebook	$20 000,00
Jan-22	Direct Marketing	AdWords	$15 000,00
Jan-22	Direct Marketing	Facebook	$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	Facebook	124	…	$3 200,00
03-Jul	Jul-21	TV	Ad Creation	125	…	$2 500,00
04-Jul	Jul-21	Direct Marketing	Facebook	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	Facebook	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	Facebook	141	…	$1 900,00
20-Jul	Jul-21	Direct Marketing	Facebook	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

[Stones]

Thank you, this resolved my issue

[v-easonf-msft]

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

[Stones]

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

[v-easonf-msft · Accepted 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.