Forum Discussion

BGB's avatar
BGB
Icon for Helper II rankHelper 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] )
)

     

BGB's avatar
BGB
Icon for Helper II rankHelper 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