# Monthly Variance Calculation

This is a discussion on Monthly Variance Calculation within the PowerPivot Questions forums, part of the Question Forums category; Hi Guys, I have used the following formula to calculate this months income accrual - last months income accrual (thus ...

1. ## Monthly Variance Calculation

Hi Guys,

I have used the following formula to calculate this months income accrual - last months income accrual (thus calculating the monthly movement);

=CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],0,month))-CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],-1,month))

It works great when looking at a monthly summary (ie just months as row labels) but when I add contract to the row labels iand move months to column labels it doesn't work in every case.

The problem arrises when a contract has no record for one of the particular months from which the calculation is based but If I have the accrual in both months for a particular contract then it works fine.

As a consequence, the grand total at the bottom of the pivot table is correct but if you some the variance for the contracts the total isn't correct because of the problem explained above.

Is there any way to get around this?

Thanks

2. ## Re: Monthly Variance Calculation

so in the case where one month is blank what do you get in the pivot table? I'm guessing a blank or something, but not sure if sum woudl return a 0 in which case the formula should be correct.

Wondering if you could wrap this in an IF statement to test if either of the months is blank.

3. ## Re: Monthly Variance Calculation

Hi Masplin, yes if there's not record it just returns a blank and in powerpivot, as you'll be aware, a numeric value - a blank = blank.

Have you any idea how I could solbve this with an if statement then?

Thanks

4. ## Re: Monthly Variance Calculation

Ok wasnt sure how it treated blanks. How about (not sure I've got brackets in right)

Code:
`=IF(CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],0,month))=blank(),-CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],-1,month)),IF(CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],-1,month))=blank(),CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],0,month)),CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],0,month))-CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],-1,month))))`
Might look neater to create 2 measures first

Current Accrual=CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],0,month))
Prev Accrual=
CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],-1,month))

Then the IF statement would be

=IF(Accrual[current acrual]=blank(),-accrual[previous accrual],IF(accrual[previous accrual]=blank(),Accrual[current accrual],Accrual[current accrual]-accrual[previous accrual]))

Mike

5. ## Re: Monthly Variance Calculation

Thanks Mike, I'll give it a go on Monday and let you know the results!

Thanks again

6. ## Re: Monthly Variance Calculation

Hi Mike, I gave the formula a go and it hasn't worked (ther's no error in the formula by the way). The problem is still where there's no record for a contract in a particular month. They don't appear to be treated as blanks or errors within the pivot table. Not sure where to go with it?

7. ## Re: Monthly Variance Calculation

Maybe the IF shoudl be =0 not =blank(). You would think if it tries ot sum data that doens exist it would produce blank or 0?

8. ## Re: Monthly Variance Calculation

I've tried a logical calculation to see if the previous month = 0 and the pt shows TRUE! However, when I then calculate the monthly variance incorporating the =0 in the appropriate places nothing changes. Proper head scratcher this one!

9. ## Re: Monthly Variance Calculation

All I can think of then is create a new table in powerpivot that has monthly rows. Calc the sums for currnet and previous month in there and do the subtraction. Then use that field in your pivot table. Not very neat but might work.

10. ## Re: Monthly Variance Calculation

Hi Mike, thanks for your help. I finally cracked it - I created a calendar with a list of all dates that my data set will cover and referenced the calendar within my calculations. Has done the trick!

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•