Allocate rest ammount

[Sergun70]

I have allocated planned budged over time proportionally  up to end date and i have fact column and know i don't know how to allocate differenece of planned budget and actua from last transactionl up to end date. Help will be very appreciated!

[parry2k]

@Sergun70can you provide soem sample data and expected result it will help for better support

[Sergun70]

My data looks like this:
Project budget:	12000
	Budget equally allocated	Actual
Jan	1000	900
Feb	1000	900
Mar	1000	900
Apr	1000	900
May	1000	900
Jun	1000	900
Jul	1000	900
Aug	1000
Sep	1000
Oct	1000
Nov	1000
Dec	1000
Total	12000	6300
Desired result:
Month	Budget allocated	Actual
Jan	1000	900
Feb	1000	900
Mar	1000	900
Apr	1000	900
May	1000	900
Jun	1000	900
Jul	1000	900
Aug	1000	1140
Sep	1000	1140
Oct	1000	1140
Nov	1000	1140
Dec	1000	1140
Total	12000	12000

[v-yulgu-msft]

Hi @Sergun70,

If the [Month] column in dataset is sorted in correct order (Jan, Feb, Mar, Apr etc), please add an index column in Query Editor mode.

If the [Month] column is sorted in irregular order, (like, Jan, Mar, Nov, etc), please add a conditional column in Query Editor to list MonthNo for each Month name.

Then, in report view, create calculated column.

Actual 2 =
IF (
    'Project budget'[Index] > MONTH ( TODAY () ),
    (
        CALCULATE (
            SUM ( 'Project budget'[Budget equally allocated] ),
            ALL ( 'Project budget' )
        )
            - CALCULATE (
                SUM ( 'Project budget'[Actual] ),
                FILTER ( 'Project budget', 'Project budget'[Index] <= MONTH ( TODAY () ) )
            )
    )
        / ( 12 - MONTH ( TODAY () ) ),
    'Project budget'[Actual]
)

To get your desired output, you can create a calculated table as well.

Result Table =
SELECTCOLUMNS (
    'Project budget',
    "Month", 'Project budget'[Month],
    "Budget allocated", 'Project budget'[Budget equally allocated],
    "Actual", 'Project budget'[Actual 2]
)

Best regards,

Yuliana Gu

[Sergun70]

Thanks a lot.

But this is not exactly what I want. I have separate date table and I have subheaders with data in month.

I want to allocate remainder of budget to future monthes where I haven't actual data.

Budget is allocated with measure:

DIVIDE (
CALCULATE ([GL_1.AMT];
FILTER (
'Headers_2';
'Headers_2'[Начало] <= MAX('Dates'[Date] )
&& 'Headers_2'[Окончание] >= MAX ('Dates'[Date])))
; (COUNTROWS (
DATESBETWEEN (
'Dates'[Date]
; FIRSTDATE('Headers_2'[Начало])
; LASTDATE (Headers_2[Окончание] )
)))
)

	Budget	Actual
jan
mat	618 078,43	2 473 359,46
pcs	1 579 185,22	64 03 622,48
etc	283 910,50	39 420,71
prof	621 912,38
exp	6 11,24
labour	1 071 360,39	406 201,90
feb
mat	558 264,38
pcs	1 426 60,84	3 2 660,08
etc	26 435,29	449 22,88
prof	561 77,31
exp	5 546,93
labour	967 680,35	1 630 730,48

mar

......