0

Assume Financial Quarters always start on the 1st of a month and they are always 3 calendar months long.

Different organisations start their Financial Year (FY) in different months - some may be 1st April , some may be 1st July or could be just 1st Jan (which will match normal Calendar Quarters).

Given a date and a month that the FY starts on how can you determine the start of the quarter that the date falls in.

E.g.

DateTime getStartOfFinancialQtr(DateTime date, int monthFinancialYearStartsOn)

15th Jan when FY starts Jan would = 1st Jan

getStartOfFinancialQtr(new DateTime(2013,1,15), 1) == new DateTime(2013,1,1)

15th August when FY starts April would be 1st July

getStartOfFinancialQtr(new DateTime(2013,8,15), 4) == new DateTime(2013,7,1)

BUT 15th Jan 2013 when FY starts February would be 1st November 2012

getStartOfFinancialQtr(new DateTime(2013,1,15), 2) == new DateTime(2012,11,1)
Ryan
  • 22,869
  • 23
  • 81
  • 126
  • 3
    Haven't you tried anything? If not, why? If you did try anything, please post your code and explain your problems in detail. This is trivial, you basically just have to code the same steps you used manually to determine the expected values of the samples you posted. – Daniel Hilgarth Jul 16 '13 at 17:18
  • This is *still* a possible duplicate of [Nearest completed quarter](http://stackoverflow.com/questions/1947191/nearest-completed-quarter) You can *easily* modify the `QuartersInYear` method to handle *your* definition of quarter. – jason Jul 16 '13 at 17:21
  • Erm - do you want my browser history of searches, scan of paper I've got in front of me? I've not really been on stack overflow in a few months, when did it become a bearpit? :/ – Ryan Jul 16 '13 at 17:21
  • @Jason - I don't think so. Try it with the last example in the question above. – Ryan Jul 16 '13 at 17:22
  • @Ryan The second example should be `== new DateTime(2013,7,1)`, right? I'm sorry but I don't get what the second parameter is. – Andre Calil Jul 16 '13 at 17:23
  • @Andre - yes you're right, sorry. – Ryan Jul 16 '13 at 17:25
  • @Mike - sorry no - its the month that the financial year starts in. – Ryan Jul 16 '13 at 17:26
  • 1
    @Ryan: It *still* works. I can't stress this enough. Change the definition of `QuartersInYear` to `return new List() { new DateTime(year, 2, 1), new DateTime(year, 5, 1), new DateTime(year, 8, 1), new DateTime(year, 11, 1), };` in the case that the Fiscal Year starts in February. Then `DateTime d = new DateTime(2013, 1, 15); Console.WriteLine(d.NearestQuarterEnd());` *will* print `11/1/2012 12:00:00 AM` to the console. – jason Jul 16 '13 at 17:28
  • @Jason - yes it does, apologies - I missed the .Union with last years dates. I doubt I would get your LINQ search method past a code review though when this will be ran through large data sets. – Ryan Jul 16 '13 at 17:36
  • @DanielHilgarth - if its so trivial as you say, step up to the plate! – Ryan Jul 16 '13 at 17:37
  • @Ryan: First piece of advice: unlikely to matter. Second piece of advice: if LINQ is a problem with your employer, unLINQify *and* search for a new employer. Third piece of advice: if it does end up mattering, this is *very* easy to make more performant: [cache](http://stackoverflow.com/questions/1988804/what-is-memoization-and-how-can-i-use-it-in-python) the return value of `QuartersInYear`. – jason Jul 16 '13 at 17:40
  • @Jason - and with dates spanning say 30 years? Cache that and search through every single time? – Ryan Jul 16 '13 at 17:46
  • Again, this is *only* if performance is an issue and I *seriously* doubt that it is. But if it is 30 * 4 * 12 is not big a number of values to cache (and if we want to be really pedantic, 8 bytes per instance so 11.25KiB). `QuartersInYear` should cache its return value off of key `year, firstMonthOfFiscalYear` and return from cache if same key is seem otherwise, compute, cache, and return. – jason Jul 16 '13 at 17:48
  • @Jason. Wow - just Wow. So now were searching 30*4*12 * dataset or implementing some complex caching system? So LINQ is the solution to everything? Now I know. I'll be sure to tell my colleague who was under the impression that nothing is an answer unless it involves Regex. I'll tell him he needs to move with the time. – Ryan Jul 16 '13 at 17:50
  • @Ryan: No, we aren't *ever* searching through that many values. You're searching through *at most* eight values. And those eight values are either computed on the fly, or returned from a cache. If your data set spans thirty years, that cache contains at most 30 * 4 * 12 different values. But the searching mechanism only searches through eight values at any given time whether or not a cache is used. – jason Jul 16 '13 at 17:51
  • Regex on the other hand, much like bourbon, is the cause of and solution to all of life's problems. – jason Jul 16 '13 at 17:52
  • I just created one million random `DateTime` instances with years ranging from 1900 to 2100, uniformly chosen, and computed nearest quarter ends using my method with *random* fiscal-year start months, and on my pathetic little corporate-issued laptop execution took just under four seconds, whether or not I used caching. – jason Jul 16 '13 at 17:58
  • If I *tighten* the range of dates to a thirty-year period so that we are more likely to utilize the cache...execution still takes just under four seconds. – jason Jul 16 '13 at 18:00
  • Switching the order of the generation of the quarter-ends, ordering their values in the `QuartersInYear` method, utilizing these two orderings by switching to `SkipWhile` then `First` halves the execution time over one million random instances to just under two seconds on the same machine. – jason Jul 16 '13 at 18:50
  • @Ryan: Added an answer. – Daniel Hilgarth Jul 16 '13 at 18:51

4 Answers4

6

The following solution is the most simple implementation I could think of and works without any - unnecessary - loops:

DateTime getStartOfFinancialQtr(DateTime date, int monthFinancialYearStartsOn)
{
    var actualMonth = date.Month;
    var financialYear = date.Year;
    var difference = actualMonth - monthFinancialYearStartsOn;
    if(difference < 0)
    {
        --financialYear;
        difference += 12;
    }
    var quarter = difference / 3;

    return new DateTime(financialYear, monthFinancialYearStartsOn, 1).AddMonths(quarter * 3);
}
Daniel Hilgarth
  • 159,901
  • 39
  • 297
  • 411
3

Isn't it as simple as this? Am I missing something to this? A quarter is defined as a period of three months, so you just have to find where the given date is, and then compute where the quarter begins based off that given month of the date.

public DateTime GetStartOfFinancialQtr(DateTime dtGiven, int startMonth) {
    DateTime dtQuarter = new DateTime(dtGiven.Year, startMonth, 1);

    // Start Q is less than the given date
    if(startMonth > dtGiven.Month) {
        while(dtQuarter > dtGiven) {
            dtQuarter = dtQuarter.AddMonths(-3);
        }
    }
    // Start Q is larger than the given date
    else {
        while(dtQuarter.Month + 3 <= dtGiven.Month) {
            dtQuarter = dtQuarter.AddMonths(3);
        }
    }

    return dtQuarter;
}

Below is the testing I ran:

Console.WriteLine(GetStartOfFinancialQtr(new DateTime(2013, 1, 15), 1).ToString());
Console.WriteLine(GetStartOfFinancialQtr(new DateTime(2013, 8, 15), 4).ToString());
Console.WriteLine(GetStartOfFinancialQtr(new DateTime(2013, 1, 15), 2).ToString());

Console output:

01/01/2013 000000
07/01/2013 000000
11/01/2012 000000
Ryan
  • 22,869
  • 23
  • 81
  • 126
Bob.
  • 3,754
  • 2
  • 42
  • 73
  • Very close, thanks - Doesn't work with 1st July if FY starts April (4) - missing <= on second while loop. – Ryan Jul 16 '13 at 18:25
3

You can use the Year class of the Time Period Library for .NET:

// ----------------------------------------------------------------------
public void FiscalYearRange()
{
  // calendar
  TimeCalendar fiscalYearCalendar = new TimeCalendar(
    new TimeCalendarConfig
      {
        YearBaseMonth = YearMonth.April,
        YearType = YearType.FiscalYear
      } );

  // time range
  TimeRange timeRange = new TimeRange( new DateTime( 2007, 10, 1 ), new DateTime( 2012, 2, 25 ) );
  Console.WriteLine( "Time range: " + timeRange );
  Console.WriteLine();

  // fiscal quarter
  Console.WriteLine( "Start Quarter: " + new Quarter( timeRange.Start, fiscalYearCalendar ) );
  Console.WriteLine( "End Quarter: " + new Quarter( timeRange.End, fiscalYearCalendar ) );
  Console.WriteLine();

  // fiscal year
  Year year = new Year( timeRange.Start, fiscalYearCalendar );
  while ( year.Start < timeRange.End )
  {
    Console.WriteLine( "Fiscal Year: " + year );
    year = year.GetNextYear();
  }
} // FiscalYearRange
1

As mentioned, you can easily obtain the answer from Nearest Completed quarter. Here's how you make the modification:

public static class DateTimeExtensions {
    public static DateTime NearestQuarterEnd(
        this DateTime date,
        int firstMonthOfFiscalYear
    ) {
        IEnumerable<DateTime> candidates =
            QuartersInYear(date.Year, firstMonthOfFiscalYear)
                .Concat(QuartersInYear(date.Year - 1, firstMonthOfFiscalYear));
        return candidates.SkipWhile(d => d > date).First();
    }

    static Dictionary<Tuple<int, int>, List<DateTime>> dict =
        new Dictionary<Tuple<int, int>, List<DateTime>>();
    static IEnumerable<DateTime> QuartersInYear(
        int year,
        int firstMonthOfFiscalYear
    ) {
        Contract.Requires(firstMonthOfFiscalYear >= 1 
            && firstMonthOfFiscalYear <= 12);
        var key = Tuple.Create(year, firstMonthOfFiscalYear);
        if(dict.ContainsKey(key)) {
            return dict[key];
        }
        else {
            var value =
                Enumerable
                  .Range(0, 4)
                  .Select(k => firstMonthOfFiscalYear + 3 * k)
                  .Select(m => m <= 12 ? m : m % 12)
                  .Select(m => new DateTime(year, m, 1))
                  .OrderByDescending(d => d)
                  .ToList();
            dict.Add(key, value);
            return value;
        }
    }
}

Usage:

 Console.WriteLine(new DateTime(2013, 1, 15).NearestQuarterEnd(1));
 Console.WriteLine(new DateTime(2013, 8, 15).NearestQuarterEnd(4));
 Console.WriteLine(new DateTime(2013, 1, 15).NearestQuarterEnd(2));

Output:

1/1/2013 12:00:00 AM
7/1/2013 12:00:00 AM
11/1/2012 12:00:00 AM

This passes all three of your test cases.

Community
  • 1
  • 1
jason
  • 220,745
  • 31
  • 400
  • 507
  • + 1 as its neat and works but I've chosen not to mark as answer as I think its overkill for this - both in terms of *potential* performance but more importantly cognitive load. Its too damn clever for me to understand without going into a darkened room. Maybe I am just lazy and dumb as @Daniel points out – Ryan Jul 16 '13 at 18:29
  • Performance is a non-issue. Stop believing the myths that LINQ isn't performant, if that's what is going on. – jason Jul 16 '13 at 18:36
  • Additionally, you'll eventually come to find that declarative code is *usually* easier to understand than procedural code. Tell the computer what you want to do, instead of how you want to do it. The whole reason for proceeding down a path like this is that it separates the "nearest quarter finding" mechanism from the "what are the quarter start dates for a given year" mechanism exactly so that you can easily handle odd quarter start dates. The procedural method is not fun to maintain when you run into quarters that are sometimes on the 28th, sometimes on the 30th, sometimes on the 31st, etc. – jason Jul 16 '13 at 18:37
  • But the declarative code is *much* easier to maintain *if* you ever have to expand to handle "unusual" quarter start dates. Again, that's the whole point to separating the two mechanisms. – jason Jul 16 '13 at 18:38
  • yes if this were for unusual quarters I would grant you that but doesn't this strike you as over engineering? (and I know you're going to throw premature optimization back at me ;) In simple LoC terms its < 10 v 30 odd. Anyway I'll take the checkmark off all answers and let the community decide. – Ryan Jul 16 '13 at 19:00
  • Oh, I don't know but that is why I stressed the word "*if*" in my previous comment. I *do* use my particular method because I've had to deal with all varieties of quarter definitions, and I did win by separating the "nearest quarter finding" mechanism from the "what are the quarter start dates for a given year." It might not be the case that your definition of quarters will ever change, in which case, yeah, maybe something else is "better". But I'll tell you, the world is a terribly messy place, *especially* when it comes to dates. – jason Jul 16 '13 at 23:39
  • Also, you can strip down the LOC by getting rid of the cache; it doesn't speed things up as I mentioned in my other [comments](http://stackoverflow.com/questions/17682949/calculate-the-start-date-of-a-financial-quarter-a-date-is-in#comment25762856_17682949). – jason Jul 16 '13 at 23:40
  • And then the last thing I'd say is that it seems the community has [already decided](http://stackoverflow.com/questions/1947191/nearest-completed-quarter/1947259#1947259). The only reason I reposted here is because you seem unconvinced that that previous answer applied to your situation here. Either way, thank you for the enjoyable dialogue, sincerely. – jason Jul 16 '13 at 23:42