Dealing with different financial calendars in companies

Velixo NX has proper support for reporting on companies with different financial calendars, and building consolidated reports for such companies.

Important Notice:

Using the function arguments (UseMasterFinancialCalendar in GL/PM functions and the Company argument in FINANCIALPERIOD... functions) requires publishing the new Generic Inquiries that supporting the functionality:

VelixoReportsPro-GLHistoryMaster
VelixoReportsPro-GLHistoryLastActivityMaster
VelixoReportsPro-PMHistoryMaster
VelixoReportsPro-FinPeriodsV2

These inquiries are available as part of our updated ERP Customization Package. To add these inquiries, contact your ERP administrator.

If you try to use the functionality without having the inquiries published, you will see an error message that guides you towards the current help page:

Master financial calendar support is not set up on your ERP instance. Please leave the argument blank. To learn more, go to http://velixo.com/help/calendar

Behavior with the “UseMasterFinancialCalendar” argument omitted or set to FALSE

GL / PM balance formulas

Historically, when you requested e.g. an ACCOUNTENDINGBALANCE, it used the company's own calendar to retrieve the balance value. For example, if you specified the financial period argument as "12-2024", and that financial period corresponded to August in the company's calendar, you would receive the value for August.

Unfortunately, that also meant that if different companies had different financial calendars (for example, some started their financial year in September, some in January, and some in October) - when you used an ACCOUNTENDINGBALANCE formula for 12-2024 and omitted the branch/company parameter, it would aggregate different calendar months together, which makes no business sense.

Prior to Velixo introducing the argument, the only workaround to build consolidated reports for companies with different financial year structure was to write separate balance formulas while specifying the financial period argument in such a way that for all those formulas, it corresponds to the same calendar month.

For example, if you had two companies, COMPANY1 starting its financial year in January, and COMPANY2 starting in September, you would have to write the following consolidating formula for August:

CODE

=ACCOUNTENDINGBALANCE(..., "COMPANY1", ..., "08-2024", ...)
+ACCOUNTENDINGBALANCE(..., "COMPANY2", ..., "12-2023", ...)

FINANCIALPERIOD... formulas

On the other hand, and perhaps somewhat counter-intuitively, FINANCIALPERIOD... formulas were always working with the master financial calendar of the Acumatica tenant and were not aware of any shifts in the company's financial calendar.

For example, if you used FINANCIALPERIODENDDATE("12-2024"), you would always receive "12-31-2024" and would have no way of requesting the end date as per the company's calendar. In the example above for COMPANY2, the end date of "12-2024" should be "31 August 2025", but you had no way of retrieving that value through Velixo.

Behavior with the “UseMasterFinancialCalendar” argument set to TRUE

Easier consolidating GL / PM balance formulas

In recent versions of Velixo, GL/PM balance functions such as ACCOUNT...BALANCE, ACCOUNTTURNOVER, ACCOUNTS...HISTORY, PROJECT...QUANTITY, PROJECT...AMOUNT expose an additional optional argument: UseMasterFinancialCalendar which significantly eases consolidated reporting on companies with different financial calendars.

However, by setting it to TRUE, the two-company example above can be simplified to just:

CODE

=ACCOUNTENDINGBALANCE(..., "COMPANY1;COMPANY2", ..., "08-2024", ..., TRUE)

This would retrieve the balance value using the master financial calendar, where 08-2024 means "August 2024" regardless of the company.

Easier financial period arithmetic using FINANCIALPERIOD... formulas

On the other hand, formulas like FINANCIALPERIODENDDATE have an optional Company argument, which allows you to do period arithmetic using a specific company's calendar. In our hypothetical setup, the following formula:

CODE

=FINANCIALPERIODENDDATE("12-2024", "COMPANY1")

will return "08-31-2025".

Summary

Velixo NX is proud to finally bridge a long-standing gap between GL/PM formulas, which historically used the company's financial calendar, and FINANCIALPERIOD... functions, which historically used the master one. That produced inconveniences in multi-calendar environments.

With the UseMasterFinancialCalendar and Company arguments you can finally build both consolidated and per-company reports with ease!