cancel
Showing results for
Did you mean: New Member

## 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:

Total Budgets = CALCULATE(sum(fBudget[Budget]),TREATAS(values(fActuals[Year]),fBudget[Year]))

I then calculate the Monthly Budgets via a measure as:
Total Budget Per Month = [Total Budgets]/12

I succeed to calculate the cumulative Actuals as:
Cumulative Actuals = CALCULATE([Total Actuals],filter(ALLSELECTED(dMonth),dMonth[Number] <= max(dMonth[Number])))
with dMonth a table with all the months and their respective month number

But when I use the above to calculate the cumulative it does not give the expected result:
Cumulative Totals = CALCULATE([Total Budget Per Month],filter(ALLSELECTED(dMonth),dMonth[Number] <= max(dMonth[Number])))
The above formula just results in the monthly budget again, but nothing is added up per month?

What am I doing wrong?

Thanks,

2 REPLIES 2  Solution Sage

Hi @m0322701 ,

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:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERUBfB-eylBNlgY9K2...

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.  Resolver II  Announcements #### Welcome to the User Group Public Preview  