locked
Excel Power Pivot moving balance from previous month RRS feed

  • Question

  • Hi All,

    I have the workbook in excel 2010 using powerpivot and im trying to calculate how a subsidy amount $700,000 moves month by month. I'm only able to calculate based on the total value but not on monthly based. We have 4 programs with no of products which vary month by month. Currently the subsidy amount 700,000 is being used every month which is not correct. The subsidy amount from the previous month should be used to calculate the subsidy amount for the current month.

    If you look at this the workbook and screenshot in the link below you will get a better idea.

    https://app.box.com/s/jd6e1p1o0rcxb12yoqqh9fp53tg02v7b

    https://app.box.com/s/5vyrfnr7vfur6mpz0xqp7oues0jidnor

    Thanks


    • Moved by L.Hl Friday, March 27, 2015 2:32 AM power pivot
    • Edited by jaggy99 Sunday, March 29, 2015 9:50 AM
    Thursday, March 26, 2015 12:27 PM

Answers

  • Hi jaggy99,

    I don't think that Power Pivot is your tool of choice here as you're doing different things in the first month compared to the following months:

    First Month: Apply an allocation of your initial value by the relation of the number of products in the first month and calculate difference to Profit of that period

    Following months: calculate difference between Profit of the period and remaing Balance per allocation of the first month.

    Things like that are complicated in DAX, you need time intelligence functions, therefore you need tables and fields that match.

    Nothing I feel able to teach a beginner in a forum.

    I would definitely not use PowerPivot for this task - cannot see anything wrong with our approach in your sample Excel sheet.

    What is your problem with that approach?


    Imke

    Sunday, March 29, 2015 12:51 PM
    Answerer

All replies

  • Excel 2010 with free PowerPivot Add-In.
    Compatible with Office 2013 Pro Plus.
    http://www.mediafire.com/view/3xq84mnmzp40svo/03_26_15b.xlsx

    Friday, March 27, 2015 12:53 AM
  • Hi Herbert,

    I have updated the attached worksheet showing how the subsidy amount should move month by month. Please have a look at this worksheet:

    https://app.box.com/s/zwrronmhxsvpv0gdj5xyfs65ckwx2o2u

    The link below shows the breakup month by month, calculated manually

    https://app.box.com/s/jd6e1p1o0rcxb12yoqqh9fp53tg02v7b

    Thanks


    • Edited by jaggy99 Sunday, March 29, 2015 11:22 AM
    Sunday, March 29, 2015 10:04 AM
  • Hi jaggy99,

    I don't think that Power Pivot is your tool of choice here as you're doing different things in the first month compared to the following months:

    First Month: Apply an allocation of your initial value by the relation of the number of products in the first month and calculate difference to Profit of that period

    Following months: calculate difference between Profit of the period and remaing Balance per allocation of the first month.

    Things like that are complicated in DAX, you need time intelligence functions, therefore you need tables and fields that match.

    Nothing I feel able to teach a beginner in a forum.

    I would definitely not use PowerPivot for this task - cannot see anything wrong with our approach in your sample Excel sheet.

    What is your problem with that approach?


    Imke

    Sunday, March 29, 2015 12:51 PM
    Answerer
  • I'm not an expert in PowerPivot but this issue is broken into two parts

    1. Calculation to move subsidy balance to subsidy allocation row in next month
    2. Calculation to get the correct subsidy balance which is actually the difference between Subsidy Allocation and Diff Income & Expense

    Thanks

    Tuesday, March 31, 2015 2:35 AM