By using this website, you agree to our Terms of Use (click here)
Hi Everyone,
My boss wants me to create a rolling 12 month Profit & Loss report.
So, if I run it for June, 2017, it should show me a column for June, 2017, then May, 2017, then April, 2017, all the way back to June 2016 (12 months trailing).
Does anyone know if this is possible?
Hi Beaker,
Yes, you should be able to do this.
I'm going to give you some screenshots of what it would look like for 3 months trailing, but you can continue the example all the way out to 12 months.
All the work will be done in the Column Sets screen.
The important part is the Data Source row on the very bottom, pictured below.
In the screenshot above you can see that Column B would pull June, 2017 in your example, Column C would pull May, 2017, Column D would pull April, 2017, and Column E would pull March, 2017.
To set the values in the Data Source field, you have to click into the magnifying glass.
Once you do that, this is what you would set for the Data Source in Column B:
This is what you would set for the Data Source in Column C (note the two -1 values):
This is what you would set for the Data Source in Column D (note the two -2 values):
This is what you would set for the Data Source in Column E (note the two -3 values):
That would take care of pulling the correct numbers, but then it would be nice to have the correct column headings as well.
Assuming that you would be running the report by populating the Start Period field, you could use the following formulas for column headings:
Column B: =Format('{0:MMM yyyy}',DateAdd(CDate(Left(@StartPeriod,2)+'/1/'+Right(@StartPeriod,4)),'m',0))
Column C: =Format('{0:MMM yyyy}',DateAdd(CDate(Left(@StartPeriod,2)+'/1/'+Right(@StartPeriod,4)),'m',-1))
Column D: =Format('{0:MMM yyyy}',DateAdd(CDate(Left(@StartPeriod,2)+'/1/'+Right(@StartPeriod,4)),'m',-2))
Column E: =Format('{0:MMM yyyy}',DateAdd(CDate(Left(@StartPeriod,2)+'/1/'+Right(@StartPeriod,4)),'m',-3))
Acumatica Version 6.10.0472 used when composing this response
@timrodman Would this format work as well in the other direction, i.e. March 2017 April 2017... February 2018?
If you cross over a fiscal year boundary will the values returned include the year end income summary reversal of year to date values?
I haven't found any documentation on the mechanics of that year end entry.
@michael-peck I haven't tried it, but you could try +1, +2, etc. instead of -1, -2, etc. Logically, it sure seems like it should work.
I was just getting individual months here so the fiscal year shouldn't matter. You could do a calculated column that totals the columns, then you don't have to use any kind of YTD logic which I agree could get messed up when you cross a fiscal year boundary.
@timrodman So for a Company with a Fiscal Year that is April 2020 through March 2021, If they wanted a P&L Report that included October 2020 through September 2021 they would bring in each Month, beginning with October 2020 in column B as Amount Type 'Turnover' then a calculated column N, to sum columns B through M should work?
@timrodman What is the full syntax of the Visible Formula shown in the first screenshot, above?
I am setting up a report with columns for each Month, using the following (just for two months, but the report will be for the full year)
When I add these strings to the appropriate columns in the Column Set, I get an error message that there is a 'Syntax error in the expression'.
I don't see what is wrong with the formula. I pasted the formula directly from an Excel spreadsheet, where I laid out the differences needed for each column.
When I add the formula, do I need to select an option from the Upper left Panel first? Which would I use if that is causing the problem?
This screenshot displays the full syntax I am using. The True, False actions are the Column Identifier (E...) and 0 in all cases.
IS that the correct syntax for these formulas?
@michael-peck A formula in the Visible Formula field needs to return true or false. So replace your column letters in your formulas with a 1 (for true).
Also, make sure to put = in front of the formulas.
@timrodman Sorry. The screen shot is from an excel spreadsheet which did not accept the '='. In the Column set, I did add the '='.
If the formula now appears as =IIF(Left(@StartPeriod,2)<=1 And (Left(@EndPeriod,2)>=1,1,0) with the 1 substituted for the Column designation, this should work? I made the change in one column and I am still receiving a syntax error message.
@michael-peck This example is from a few years ago so I don't still have it, but I think my formula was something like:
=IIF(Left(@StartPeriod,2)='01'
That's ok because both Left(@StartPeriod,2) and '01' are text values. If you want to do something like <=, you need to use a number value so you need to cast your text to something like an integer like this:
=IIF(CInt(Left(@StartPeriod,2))<=1
@timrodman I carried through all of the changes and I am no longer getting Syntax Errors. The Column Set begins with a Budget YTD and Actual YTD then individual Month Budget and Actual. I want the two YTD Columns to appear in every iteration, but only show the values for the Start and End Periods.
Is there any special syntax needed for those two YTD columns?
Does their inclusion in the Column set negatively affect the operation of the Visible Formula?
Very cool. Thanks Tim.
Those are some crazy formulas, but they seem to work just fine.
Hey Baraboo "Bob",
I LOVE the username! It took me a while to get it 🙂
Is it possible to have this exact same thing, except be able to specify the interval you would like it to show? By this I mean, say I specify a start period of January 2018 and an end period of June 2018, could I get the report to only show the 6 months specified and hide the other 6?
So, if you run it for January, you would get one column, if you run it for February, you would get two columns, etc.?
So still following the rolling 12 months, we have a request where the user enters a starting period and the report would return 1 column for each location (sub account) for the starting period - 11 months. I know we could "hide" the 12 columns and sum them up for each location but there are 22 locations * 13 columns = too many... perhaps there is an easier road.
So you want one column for each location and you want each column to display a total for all 12 months (11 months prior + 10 months prior + ... + current month)?
Tim,
That's correct.
Lloyd
Have you tried something like this on the Column Set?
Tim - that worked, though I'm not sure why I was getting 0's before. I must have had something else entered.
So we've talked about a rolling 12-month P&L starting at an arbitrary period, we've talked about a P&L that prints within a start and end period, and we've talked about multiple entities showing up on a P&L.
I would like to make a YTD P&L report that asks the user for Start Branch and Financial Period, and on the basis of that prints a column for every month on or before the specified period in the period's year. I would also like the report to have a column that adds up the visible columns to show the total as of the specified period.
For example, if the specified period is 04-2018, the report would print four data columns, and then add them together in the last column.
Perhaps there's no way around specifying a start period and an end period, and then having the report sum that range. Asking for just the ending period would be simpler for what I envision.
Two things that (I think) make this easier: the report would never exceed 12 period columns, and the YTD number should just be the account balance at the end of the specified period.
Here's the pseudo-code of what I'm imagining shown as a formula for each column:
=If(Month(financial.period)>12, print column Month(financial.period, -12), otherwise hide column)
=If(Month(financial.period)>11, print column Month(financial.period, -11), otherwise hide column)
=If(Month(financial.period)>10, print column Month(financial.period, -10), otherwise hide column)
=If(Month(financial.period)>9, print column Month(financial.period, -9), otherwise hide column)
=If(Month(financial.period)>8, print column Month(financial.period, -8), otherwise hide column)
=If(Month(financial.period)>7, print column Month(financial.period, -7), otherwise hide column)
=If(Month(financial.period)>6, print column Month(financial.period, -6), otherwise hide column)
=If(Month(financial.period)>5, print column Month(financial.period, -5), otherwise hide column)
=If(Month(financial.period)>4, print column Month(financial.period, -4), otherwise hide column)
=If(Month(financial.period)>3, print column Month(financial.period, -3), otherwise hide column)
=If(Month(financial.period)>2, print column Month(financial.period, -2), otherwise hide column)
=If(Month(financial.period)>1, print column Month(financial.period, -1), otherwise hide column)
=Month(financial.period)
=Account.balance(financial.period)
Any suggestions are appreciated -- I haven't done this kind of conditional logic in report writer before so I appreciate any experience you're able to share.
Daniel
Hi Daniel,
What if you did this.
Create the 12 columns for each month with conditional suppression logic.
Create 12 more columns for the totals. Each column would be to the right of the related month column that it belongs to.
So you would have 2 columns for January, 2 columns for February, etc.
The total columns could actually just be YTD calculations with the month hardcoded. For March, the Data Source would look like this:
Does that make sense?
@timrodman Thank you for all your tips putting together the Rolling 12 report! As a newbie, these forums are extremely helpful.
We are using Start Period AND End Period report parameters and would like the total column at the end to subtotal only the visible months. I tried adding additional columns next to each month showing ending balance, and that works except when Start Period isn't January (our report shows January through December left to right). Since we're using End Period as well, the Data Source has Start Period and End Period filled for each month, and not Offset. Am I missing a very obvious way to subtotal only visible months? Thanks for your help!
@stephanie-mullett would you be able to share how you made this all work? I am trying to be able to setup a P&L where I tell it what the start and end periods are, it shows me those X amount of months and the total of them....having a real tough time even getting going!
@chelsea-parent Yes happy to help, I know it can be a steep learning curve. I added a formula in the value field for each month. So for January, formula is as follows: =IIf(Left(@StartPeriod,2)<=1 And Left(@EndPeriod,2)>=1, B, 0) where B is the column for January
February is: =IIf(Left(@StartPeriod,2)<=2 And Left(@EndPeriod,2)>=2, C, 0) where C is the column for February
Then a total column that subtotals all months: =B+C+D+E+F+G+H+I+J+K+L+M. Make sure your Data Source is set as follows for each month (2 for Feb, 3 for March, etc):
Hope this is helpful!
@stephanie-mullett thank you SO much! I will give this a try and see how it goes....I appreciate the formulas so I can at least start somewhere.
That makes sense, yes. I forgot about the "ending balance" type. I took the report in a different direction, but I'll remember this if I find this need again. Thanks!
hi Guys, this is awesome, thank you, I will play around with this. However, is there any way this sort of data / setup can be shared? i.e. if I spend a heap of time putting together a cool report, can I share this with others? And if so, is there anywhere that people share reports etc? I would be happy to share any of the customisation work I am doing / have paid for if it helps someone else.
Online Members
