Cumulative Budgets and Allocations

[m0322701]

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,

[v-joesh-msft]

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.

[bpsearle]

Have a look at this:

https://community.powerbi.com/t5/Desktop/Setting-Custom-Dynamic-Quarters/m-p/831291#M399320

Does it help?