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