asked on

fiscal year end calculations

I need to perform some calculations based on our clients fiscal year ends. Not all of our clients have the same fiscal year end month. Some use December, others Sept, etc. I have a field in a table that states the month each client uses as their fiscal year end month. How would I reference this for calculations such as summing NetChargeoffs for the current fiscal year?

Emil_Gray

Nested IIf statements in a query or SELECT Case in VBA.

GRayL

Try this. The table FyEnds has two fields, ClientID, and the FyEnd indicating the month in which the fy ends:

SELECT a.ClientID, b.FyEnd, Sum(fldAmt) From ClientData a
INNER JOIN FYEnds b ON a.ClientID = b.ClientID WHERE a.EntryDate BETWEEN
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date()),b.FyEnd+1,1),DateSerial(Year(Date())-1,b.FyEnd+1,1) AND
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date())+1,b.FyEnd+1,0),DateSerial(Year(Date()),b.FyEnd+1,0);

GRayL

The first IIF() calulates the Start date of the FY and the second calculates the End date.

I'm assuming a ClientData table has several clients, multi-year dates, and one or more amount fields which you want summed by client. I just saw I neglected the GROUP BY, and don't need to see the FyEnd value:

SELECT a.ClientID, Sum(fldAmt) From ClientData a
INNER JOIN FYEnds b ON a.ClientID = b.ClientID WHERE a.EntryDate BETWEEN
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date()),b.FyEnd+1,1),DateSerial(Year(Date())-1,b.FyEnd+1,1) AND
Iif(Month(Date())>b.FyEnd,DateSerial(Year(Date())+1,b.FyEnd+1,0),DateSerial(Year(Date()),b.FyEnd+1,0)
GROUP BY a.ClientID;

GRayL

Anybody there?

Gustav Brock

Here are some helper functions which you can adjust to your needs.

The basis function is DateFYStart. As is, it takes February as FYStart.

You could change that to take the month as a parameter:

<snip>
Public Function DateFYStart(ByVal intYear As Integer, ByVal intMonth As Integer) As Date

Dim datFYstart As Date
Dim intWeekday As Integer
Dim intOffset As Integer

' Nominel start date of fiscal year.
datFYstart = DateSerial(intYear, intMonth, 1)
</snip>

To skip the full week adjustment, just remove that part of the function.

/gustav

Public Function DateFYStart(ByVal intYear As Integer) As Date
 
  Dim datFYstart As Date
  Dim intWeekday As Integer
  Dim intOffset  As Integer
 
  ' Nominel start date of fiscal year.
  datFYstart = DateSerial(intYear, 2, 1)
  intWeekday = WeekDay(datFYstart, vbMonday)
  If intWeekday <= 4 Then
    ' Use Monday before.
    intOffset = -intWeekday
  Else
    ' Use Monday next.
    intOffset = 7 - intWeekday
  End If
  ' Adjust start date to closest Monday.
  datFYstart = DateAdd("d", 1 + intOffset, datFYstart)
 
  DateFYStart = datFYstart
 
End Function
 
Public Function DateFYEnd(ByVal intYear As Integer) As Date
 
  Dim datFYend As Date
  
  ' Subtract one day from start date of next FY.
  datFYend = DateAdd("d", -1, DateFYStart(intYear + 1))
 
  DateFYEnd = datFYend
 
End Function
 
Public Function FiscalYear(ByVal datDate As Date) As Integer
 
  Dim intFYyear As Integer
  
  intFYyear = Year(datDate)
  If DateDiff("d", datDate, DateFYStart(intFYyear)) > 0 Then
    ' Fiscal year is before calendar year.
    intFYyear = intFYyear - 1
  End If
 
  FiscalYear = intFYyear
 
End Function
 
Public Function FiscalWeek(ByVal datDate As Date) As Long
 
  Dim lngFiscalWeek As Long
 
  lngFiscalWeek = DateDiff("ww", DateFYStart(FiscalYear(datDate)), datDate, vbMonday) + 1
 
  FiscalWeek = lngFiscalWeek
 
End Function