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

     

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

      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.