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

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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
Icon for Helper II rankHelper 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.