Forum Discussion
Cumulative Budgets and Allocations
Hi All,
I am struggling with calculating the cumulative of monthly allocated budgets derived from a yearly budget.
My goal is to compare actuals that I get in on a monthly basis with the corresponding budget on a monthly basis, both on a per month as well as a YTD or cumulative way.
My Actuals table "fActuals" looks like this:
YEAR (YYYY) || MONTH (MMM) || ACCOUNT || ACTUAL
My Budget Table "fBudget" looks like this:
YEAR (YYYY) || ACCOUNT || BUDGET
I calculated Total Budgets via a measure as:
2 Replies
- bpsearle
Resolver II
Have a look at this:
https://community.powerbi.com/t5/Desktop/Setting-Custom-Dynamic-Quarters/m-p/831291#M399320
Does it help?
v-joesh-msftSolution Sage
Hi Anonymous ,
The fields in table “dMonth” have nothing to do with the fields in table “fBudget”, so there is no way to display the results you want. I think you can create a new column in table “fActuals”:
Column = SWITCH([Month], "Jan", 1, "Feb", 2, "Mar", 3, "Apr", 4, "May", 5, "Jun", 6, "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec", 12 )Then create the following measure:
Cumulative Totals = IF ( HASONEVALUE ( fActuals[Column] ), SUMX ( FILTER ( ALL ( fActuals ), fActuals[YEAR] = MIN ( fActuals[YEAR] ) && fActuals[Column] <= MIN ( fActuals[Column] ) ), [Total Budget Per Month] ), [Total Budget Per Month] * 12 )Results are as follows:
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
