Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
I would like this chat above to be in cumulative form.
Please help!
Solved! Go to Solution.
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] )
)
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.
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] )
)
I change your first measure a bit and seems to work to as extent .
-------
This gives me budget expected per month
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
105 | |
98 | |
39 | |
30 |