Small and medium business | Business Central, N...

Formulae for column date

(1) Share

Report

Posted on by CU29101917-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:

Dynamics 365 Business Central

I have the same question (0)

All responses (8)

Answers (0)

tanya07 1,638 on at

Like (0)

Report
Copy link

Link copied!

Hello,

Where have you put the formula??

Was this reply helpful? Yes No
CU29101917-0 76 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Suggested answer

Valentin Castravet 31,586 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!

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/

Was this reply helpful? Yes No
CU29101917-0 76 on at

Like (0)

Report
Copy link

Link copied!

hi vaelntin,

whats the diff between balance and net change?

Was this reply helpful? Yes No
CU29101917-0 76 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Suggested answer

Valentin Castravet 31,586 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 96,039 Super User 2025 Season 2 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Suggested answer

Vinayak Vishnu Sagv... 212 on at

Like (0)

Report
Copy link

Link copied!

Difference between Balance and Net change is below

Balance will show you the total income recorded in that account since General ledger account creation.

The Net Change, however, will reveal how much income was recorded within the selected month period .

April to March or Jane to Dec depends on selection of periods.

Was this reply helpful? Yes No