Business Central forum

Formulae for column date

(1) Share

Report

Posted on by NZ-06081612-0

Creating a report by calander year .

Example, one column is 'Jan-March'

How should I enter the formula ? I tried putting FY[1..3] but its not giving data for 'Jan-March'.

Categories:

Business Central

All responses (7)

Answers (0)

Suggested answer

YUN ZHU 70,243 Super User 2024 Season 2 on at

Like (0)

Report

Formulae for column date

Hi, There is a prerequisite for using FY, have you added Accounting Periods? If not, this will have no effect.

Hope this helps.

Thanks.

ZHU
Suggested answer

Valentin Castravet 22,408 Super User 2024 Season 2 on at

Like (0)

Report

Formulae for column date

whats the diff between balance and net change?

The 'Balance' shows the ending balance of an account and is typically used for balance sheet accounts. In your case the balance will be at the end of March 31. 'Net Change' indicates the changes in balances between a beginning period and an ending period, typically for income statement accounts. In your case the net change between January and March.
FY[1..3] is only going to work if both your row and column definition is set up to get the Net Change. If either are set to Balance, its not going to work. If its set to Balance, the system will simply show you the balance at the end of that period.

FY[1..3] is for FISCAL YEAR where as I need formula for CALENDAR year.

I am aware. That's the purpose of setting up accounting periods - to make the link between a fiscal year and a calendar year for the system. What does your accounting period look like? Is your fiscal year from January to December, or is it different? You can still use FY to represent January to March; you just need to align it with your months. For example, if your accounting period starts in December, you would use FY[2..4] to cover January to March. If it starts in January, you would use FY[1..3].
NZ-06081612-0 19 on at

Like (0)

Report

Formulae for column date

hi valentin,

from link below

https://learn.microsoft.com/en-ca/dynamics365/business-central/bi-column-definitions

FY[1..3] is for FISCAL YEAR where as I need formula for CALENDAR year.

Do you know what should it be for Calendar year?
NZ-06081612-0 19 on at

Like (0)

Report

Formulae for column date

hi vaelntin,

whats the diff between balance and net change?
Suggested answer

Valentin Castravet 22,408 Super User 2024 Season 2 on at

Like (0)

Report

Formulae for column date

What you have in the comparison period formula should work. It can be one of the following:

When you run the report do you change the date filter?

Do you have accounting periods set up?

Is the value in the Column Type in the column definition correct? i.e. is it balance at date or net change

Valentin Castravet
Work: Zander ERP Services
Blog: Dynamics 365 Business Central Insights
LinkedIn: www.linkedin.com/in/valentin-c-0500a247/
NZ-06081612-0 19 on at

Like (0)

Report

Formulae for column date

Hi tanya,

im trying to put it it in compariosn period formula:

the figures im getting do not match with i have on the report.
tanya07 1,535 on at

Like (0)

Report

Formulae for column date

Hello,

Where have you put the formula??