Trending financial report

  • 22 August 2023
  • 8 replies

Userlevel 2

How do I create the column set to have a column for each month and a total for the year?  I want to be able to select the year and then have the columns for each month.   


Best answer by Laura02 22 August 2023, 21:37

View original

8 replies

Userlevel 7
Badge +6

This should do it. Create each column and then use your data Source to pick your periods for each column

Then on your Total use the 

This was a monthly PL and i did for a customer

Userlevel 7
Badge +14


You will need 12 columns that specify Period Number, followed by a Total or a YTD Balance column. This column design is a P & L with Period balances (net change) for months 01 - 12 followed by Year to Date balance for period 12.

We can’t choose ‘just a year’; in this case we print for periods 01 to 12 or we can print for period 12 and see the same results.


Userlevel 2

This worked, other than - if I run the report as of April I was hoping that only Jan-Apr columns would be filled in and then the total for 2023 would be the first 4 months of the year but all data shows for all months.  Thoughts?  I have a 2022 column too so if I put in April it shows April 2022 YTD. 


Thank you!

Userlevel 7
Badge +14


Notice the Visible Formula. you will edit the numbers in the formula for each month: 

Month 4:   =iif((@EndPeriod>='04' and @StartPeriod<='05'),True,False)

Month 5: =iif((@EndPeriod>='05' and @StartPeriod<='06'),True,False)

and so on.


Userlevel 2

This helped! Thank you! 

Userlevel 2

One more thing - my YTD column is still adding all columns (even those not visible).  How to adjust the formula to only include columns visible? 

Userlevel 7
Badge +14


A YTD column will show YTD no matter what periods are visible.

Perhaps use 12 different YTD columns (Year to date with period 01, year to date with period 02, etc) with Visible formula on each one like this for period 05:  iif((@EndPeriod='05'


Userlevel 2

I had to do a calc column for each month and then was able to get it to work.


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2023  Acumatica, Inc. All rights reserved