cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
m0322701
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
v-joesh-msft
Solution Sage
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:

11.PNG

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.

bpsearle
Resolver II
Resolver II

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors