# Excel Power Pivot moving balance from previous month

• ### 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/5vyrfnr7vfur6mpz0xqp7oues0jidnor

Thanks

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

• 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

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