Account Schedule Comparison Period Formula not working on 365 Business Central

onethreeone

Hi There

I'm having a very frustrating time with account schedules. I designed a column layout for my client in NAV 2018 which worked fine. It showed them the figures for the stated date range, the figures for the same period LY, Current YTD and LYTD.

Having been upgraded by our partner to GB NAV 14.1 connected to Dynamics 365 Business Central the formulas for the Last Year columns are no longer working.

The colunm layout setup looks like this:



The client runs accounting periods on the 4-4-5 model so I have to use Comparison Period Formulas.

Previously, if I ran an Income Statement for P1..P9 (29/10/18..28/07/19) I would get,

• in column 1, figures for that date range;
• in column 2 figures for 30/10/17..29/07/18;
• and in columns 4 & 5 the same (let's draw a veil over why the client wants YTD columns that show the same information...I thought they'd be reporting quarters separately each time, but no!)

Since the upgrade I now get the following date rages by column (bear in mind, I've made no changes to the formula)

• in column 1 no change: figures for the asked for date range are shown
• in column 2: 30/10/17..26/11/17. In other words P1 of the previous year
• in column 4 figures for the asked for date range as you'd expect
• and in column 5 30/10/17..26/11/17. In other words P1 of the previous year

I've tried every variant of the formula to get last year's figures for the equivalent period that I can imagine or can find suggested online

• -1FY
• -1FY[1..CP]
• -1FY[CP]

None of them give different results.

I've also tried changing the Column Type to Balance, Net Change or Balance at date. Nothing makes a difference.

Is this a bug in 365 Business Central or am I missing something embarrassingly obvious?

onethreeone

I’ve solved this myself (with help from a friend). What was causing the comparison period formula to fail was the fact that I was using a date range of P1..P9 on the account schedule rather than a single period (ie P9). This then returns the cumulative figures I’m after in columns 4 & 5.

*facepalm*

onethreeone

I’ve solved this myself (with help from a friend). What was causing the comparison period formula to fail was the fact that I was using a date range of P1..P9 on the account schedule rather than a single period (ie P9). This then returns the cumulative figures I’m after in columns 4 & 5.

*facepalm*