locked
divide annual budget over months RRS feed

  • Question

  • I'm still learning powerpivot but I haven't been able to find a solution for my problem:

    I work with 2 separate tables: 1 for my budget and 1 for my actuals.
    Both tables are exported from my ERP program and combined with powerpivot.
    My actuals are grouped per month (month 01 / month 02 etc...)
    My budget is annual and is uploaded in "month 01". (approx 50k records)

    I'd like to divide my annual budget over 12 months. Now I see my full budget in january, and i see no budget in the other 11 months. What i'd like to see: when filtered on june it should show 1/12th of the budget. When filtered on January to June I want to see 6/12 of my budget etc...

    The only option I see is to copy my budget 12x, divide each record by 12 and change the period 12x. But this means 600K lines, and a lot of manual work each time I want to import a new budget into powerpivot.

    Does someone have an idea how to solve my problem?

    Thursday, January 26, 2017 9:56 PM

Answers

  • Probably, in your case, you have budget values for multiple years, departments, products perhaps, etcetera. Using ALL(Budget) throws away all filters which is not what you want.

    The challenge here is to provide a budget result when you e.g. selected the month of June 2017. In this context, you'll have to find the year's budget which is in January, and multiply that with the fraction of the year you're looking at (e.g. 1/12). I assume you have a proper Calendar or Date table in your model; if not, do add one (this is pretty basic and you'll find instructions how to do this everywhere). To find the proper budget value, you'll have to remove the month filter:

    CALCULATE(SUM(Budget[Budget]), ALL(Calendar[Month]))

    this computes the budget for the whole year, which equals the January amount in your case.

    Now, to calculate the correct fraction of the year's budget, count the number of months, which is the number of distinct values in the Calender[Month] column. This is either DISTINCTCOUNT(Calendar[Month]) or COUNTROWS(VALUES(Calendar[Month])). Divide this by 12 to get the year fraction. The complete formula is

    BudgetValue:= CALCULATE(SUM(Budget[Budget]), ALL(Calendar[Month])) * COUNTROWS(VALUES(Calendar[Month]))/12

    or, using variables,

    BudgetValue:=
    VAR WholeYearBudget = CALCULATE(SUM(Budget[Budget]), ALL(Calendar[Month]))
    VAR YearFraction = COUNTROWS(VALUES(Calendar[Month]))/12
    RETURN
    WholeYearBudget * YearFraction

    Friday, January 27, 2017 8:49 AM
    Answerer

All replies

  • Hi simondw, 

    First please create a slicer including the month 01 / month 02 ---- month 12, which used to filter months.

    Create a measure, select-month:=COUNT(actual[month], ALLSELECTED (actual[month]))
    
    Expected result:=CALCULATE(sum(budget[budget]),ALL(budget))/actural[select-month]

    Then, create a pivot table, select the actual[month] as row level, the “Expected result” measure as value level. When you select the different month in slicer, it will return the desired result. For instance, when click on june it should show 1/12th of the budget. When click on January to June in slicer, select-month will return 6, so the Expected result measure will calculate the 6/12 of my all budget .

    If this is what you want, please post the sample data and expected result for further analysis.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, January 27, 2017 2:52 AM
  • Probably, in your case, you have budget values for multiple years, departments, products perhaps, etcetera. Using ALL(Budget) throws away all filters which is not what you want.

    The challenge here is to provide a budget result when you e.g. selected the month of June 2017. In this context, you'll have to find the year's budget which is in January, and multiply that with the fraction of the year you're looking at (e.g. 1/12). I assume you have a proper Calendar or Date table in your model; if not, do add one (this is pretty basic and you'll find instructions how to do this everywhere). To find the proper budget value, you'll have to remove the month filter:

    CALCULATE(SUM(Budget[Budget]), ALL(Calendar[Month]))

    this computes the budget for the whole year, which equals the January amount in your case.

    Now, to calculate the correct fraction of the year's budget, count the number of months, which is the number of distinct values in the Calender[Month] column. This is either DISTINCTCOUNT(Calendar[Month]) or COUNTROWS(VALUES(Calendar[Month])). Divide this by 12 to get the year fraction. The complete formula is

    BudgetValue:= CALCULATE(SUM(Budget[Budget]), ALL(Calendar[Month])) * COUNTROWS(VALUES(Calendar[Month]))/12

    or, using variables,

    BudgetValue:=
    VAR WholeYearBudget = CALCULATE(SUM(Budget[Budget]), ALL(Calendar[Month]))
    VAR YearFraction = COUNTROWS(VALUES(Calendar[Month]))/12
    RETURN
    WholeYearBudget * YearFraction

    Friday, January 27, 2017 8:49 AM
    Answerer
  • Hi Angelia and Michiel,

    Thanks for the help.
    I've tried both solutions, but only the one from Michiel seems to work for me.

    In the meanwhile I figured out how to use a pro rata budget instead of 1/12th per month.
    For example: the average sales over the last 10 years in January are 5% of my annual budget, while those in february are 15%. When both selected it shows 20%. I Managed to do this by adding a percentage column to the months in my calendar table and changing the formula Michiel provided.

    Now i'm facing other challenges:

    Bit background info on my budget:
    I have annual budget values for both income and expenses for different companies, different divisions, different products, etc... (these are all columns in my data). I've calculated which percentage of their budget each company meets in which month on average over the last 10 years. 

    Challenge 1:

    I have a table with the actuals, with a "company column"
    I have a table with the Budget with a "company column"
    I have a Table with XX companies.
    (The company columns in the actuals and budget are  in a "many to one" relationship with the company table).

    It's obvious that the different companies won't sell the same amount each month.
    Now the budget for each company shows 5% in january.
    What i'd like is that it shows 5% "for company A", and 10% for "company B" in January.
    Is there a way to set a custom pro rata percentage for each company for each month? (Remember, the budget is in January in my budget table).

    Challenge 2: (Similar to the one above)

    Table with actuals with a column that states if the records are expense or income
    Table with budget with a column that states if the records are expense or income
    Table with "expense / income" so I can make a relationship between my actuals and budget.

    Is it possible to set a custom pro rata percentage for each type "expense or income" for each month?
    Can I combine this with the different percentages per company (See above)?

    Expected result:
    "The target for Company A, based on historical averages should be 5% of it's annual expense budget in january, while the income target is 10% of it's annual budget. Company B should reach 1% of it's annual expense budget in january, while 5% of it's annual income budget should be met.

    Best Regards,

    Simon





    • Edited by simondw Sunday, January 29, 2017 1:06 AM
    Saturday, January 28, 2017 10:31 PM
  • Since your company table is unique, this would be the logical place to put your distribution parameters for each one inorder to use that value/month combo to spread the budgets. If you need to spread income and expenses at different rates, build an column set for both. If a number of companies are similar, you could normalize them by creating lookup tables. Use a key for say Christmas Line, Summary Line, Even Line and have just the key in the company table. You could have one for Revenues and another for Expenses. 

    With the factors you can then apply them to the budgeted by type for each month as total x factor.

    Tuesday, February 14, 2017 5:34 PM