Monthly Variance Calculation

[JEB85]

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

 

[masplin]

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.

 

[JEB85]

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

 

[masplin]

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

[SIZE=2]=IF([/SIZE][SIZE=2]CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],0,month))=blank(),[/SIZE][SIZE=2]-CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],-1,month)),IF([/SIZE][SIZE=2]CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],-1,month))=blank(),[/SIZE][SIZE=2]CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],0,month)),[/SIZE][SIZE=2]CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],0,month))-CALCULATE(sum(Accrual[Accrual]),PARALLELPERIOD(Accrual[Month],-1,month))))[/SIZE]

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

 

[JEB85]

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

Thanks again

 

[JEB85]

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?

 

[masplin]

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?

 

[JEB85]

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!

 

[masplin]

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.

 

[JEB85]

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!