Running Total Based on Budget Division by Duration

[BGB]

Hi There,

I need a solution to this problem, please.

My dataset looks like below

So I need to split the total budget into number of months from the start date to the end date. Which is divide (total budget, duration (months) which gives me the budget expected to spend per month.

However, I need to do a running total on this budget per month to show how much the budget should have been spent.

I used this formula below and the result isn't accumulating

Forecast Budget running total in Date =

VAR Budgetamtpermnth = DIVIDE(Sum(Sheet1[budget]),[User Duration Months])

VAR Calc=

CALCULATE(

     Budgetamtpermnth ,

    FILTER(

        ALLSELECTED('DateTable'[Date]),

        'DateTable'[Date] <= MAX('DateTable'[Date]) )

    )

Return Calc

I would like this chat above to be in cumulative form.

Please help!

[BGB]

This is the measure I finally used however I need to mark @tamerj1 answer as correct as it pointed me to the right direction

Forecast Budget running total in Date =

VAR MaxEnddate = MAXX(Sheet1,Sheet1[End Date])

VAR Calc=

CALCULATE (

SUMX (

VALUES ( 'DateTable'[Month-Year] ),

CALCULATE ( [Forcast Budget To end date] )

),

ALLSELECTED ( 'DateTable' ),

'DateTable'[Date] <= MAX('DateTable'[Date] )

)

VAR Calc1 = CALCULATE(Calc,Sheet1[Start Date] <=MaxEnddate)

VAR Calc2 = if(ISBLANK([Forcast Budget To end date]),BLANK(),Calc1)

Return Calc2

[tamerj1 · Accepted solution]

Hi @BGB 

please try

Forecast Budget running total in Date =
CALCULATE (
    SUMX (
        VALUES ( 'DateTable'[Month-Year] ),
        CALCULATE ( DIVIDE ( SUM ( Sheet1[budget] ), [User Duration Months] ) )
    ),
    ALLSELECTED ( 'DateTable' ),
    'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
)

[BGB]

@tamerj1  Thanks for replying to this query.

This almost worked, except that the incremental is not on the user ID level. For example, in the picture below, the increment should start from 94.5 and go up each month by that value, as the top visual shows. However, I think this is going up by all the budget given for all users which is why its in thousands already.

I'm guessing there is something we need to add, probably sumx or maxx or even change where we have applied sumx and maxx in the measure.

Thanks for your help so far. Looking forward to hearing from you.

[tamerj1]

@BGB 

Please try

Forecast Budget running total in Date =
CALCULATE (
    SUMX (
        CROSSJOIN ( VALUES ( 'DateTable'[Month-Year] ), VALUES ( Sheet1[User] ) ),
        CALCULATE ( DIVIDE ( SUM ( Sheet1[budget] ), [User Duration Months] ) )
    ),
    ALLSELECTED ( 'DateTable' ),
    'DateTable'[Date] <= MAX ( 'DateTable'[Date] )
)

[BGB]

@tamerj1 

I change your first measure a bit and seems to work to as extent .

Forecast Budget running total in Date =

CALCULATE (

SUMX (

VALUES ( 'DateTable'[Month-Year] ),

CALCULATE ( [Forcast Budget To end date] )

),

ALLSELECTED ( 'DateTable' ),

'DateTable'[Date] <= MAX('DateTable'[Date] )

-------

This gives me budget expected per month

Forcast Budget To end date =

CALCULATE(

[Forecast Budget]

,FILTER(

Sheet1

,(Sheet1[Start Date] <= MAX(DateTable[Date])

&& Sheet1[End Date1]> MAX(DateTable[Date])

)))

----

New Result

The only thing now is, I would like the budget to stop from on the enddate month and not go on till the end of my calendar table. This is so each user have a dynamic end date based on the entered end date.