Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BGB
Helper II
Helper II

Running Total Based on Budget Division by Duration

Hi There,

I need a solution to this problem, please.

 

My dataset looks like below

BGB_0-1655720408286.png

 


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


BGB_1-1655720408343.png

 

 

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

 

Please help!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

 

View solution in original post

5 REPLIES 5
BGB
Helper II
Helper II

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
 
 
BGB_0-1655757185930.png

 

tamerj1
Super User
Super User

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  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.

BGB_0-1655725290080.png

 



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

@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
BGB_0-1655729840694.png

 

 

 

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors