Calculating month movement and returning blanks

JEB85

Board Regular
Joined
Aug 13, 2010
Messages
238
Hi,

My powerpivot output is currently summarised as follows (the desired column is the result i would like):

FinancialYearMonthNameBalance SheetMovementDesired
2015Sep1,3441,344
Oct1,570226226
Nov1,486-84-84
Dec1,5264040
Jan1,079-447-447
Feb1,480401401
Mar1,882402402
2016Apr1,051-831-831
May1,880829829
Jun1,549-331-331
Jul1,691142142
Aug1,7404949
Sep1,7642424
Oct1,764

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"></colgroup><tbody>
</tbody>

The 2 measures i have calculated are as follows:
Balance Sheet =CALCULATE(sum(BalanceSheet[Accrual (£)]))
Movement =CALCULATE(sum(BalanceSheet[Accrual (£)]),PARALLELPERIOD('Calendar'[Date],-1,MONTH))-[Balance Sheet £]

So i'm calculating the month movement on the balance sheet.
My data set begins in Sep-15 and will be updated on a monthly basis - the last set of data i have is Sep-16.
I would like the first month's movement to return blank (Sep-15 in this case) as essentially it's calculating current month - prior month balance sheet when there is no prior month's balance sheet.
Similarly, i would like to return a blank in Oct-16 as it's not a real value (i don't have any data for Oct-16).
The only complication is that my data set is at a product level and we have new products come in each month so if i pivot by product i don't want to return a blank if the product didn't exist in the prior month.

Is there a way to return the blanks i desire?

Thanks
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,215,131
Messages
6,123,223
Members
449,091
Latest member
jeremy_bp001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top