0

Function/Formula to find previous period ending date

Jet Reports Historic Posts

January 04, 2011 07:44

None

Follow

Hi People. Happy New Year!

I have this problem with some of my reports where I want to find the previous period ending date. The reason is because I calculate opening balances.

I found EDATE(END,-12) to be the best way to calculate it for 12 months period but there is a problem when I run the report for a company that has a different period length.

I'm thinking of something that will find the new fiscal year date for the period and then subtract 1 day.

Thanks in advance :P

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts January 04, 2011 18:55

  Hi Jackies,
  
  Are you a NAV or GP user? I would suspect GP from the description of your problem. I think the GP tables you are looking for would be the Period Header (SY40101) and Period Master (SY40100). Those should have the fiscal years and periods in them along with the start and end dates.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 05, 2011 07:05

  Hi fhilton,
  
  I'm a NAV user. ;)

• 0
  
  Jet Reports Historic Posts January 05, 2011 15:44

  In that case, you probably need the Accounting Period table (table number 50). It doesn't specifically contain the ending dates of the years/periods, but you could get the starting date of the current period and subtract 1 from it to get the end date of the previous period. Something like this:
  
  =NL("Last","Accounting Period","Starting Date","Starting Date",NP("DateFilter",,TODAY()))-1
  
  Does something like that work?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 10, 2011 10:34

  Worked with a slight change in the NP formula:
  
  =NL("Last","Accounting Period","Starting Date","Starting Date",NP("DateFilter",D6,),,,,,,,,,,,,,,,,,"company=",$D$10)-1
  
  where D6 is the Starting date in my filter.
  
  Thanks fhilton :D
  
  EDIT: Well…not as I wanted it to work :cry:
  I will make some changes and get back.