Forum Discussion

BGB's avatar
BGB
Helper II
4 years ago
Solved

Running Total Based on Budget Division by Duration

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!

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

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

     

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      BGB
      Helper II
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

        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's avatar
    BGB
    Helper II
    4 years ago
    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