c# linq statement to calculate balance over 12 months

Question

I'm trying to plot a flot chart. I want the x axis to be months from january to december and y axis to be account balances. I have the income and expense of the account over 12 months time but subtracting them would only give me the difference for the month it does not add the balance from the previous month.

Here is how I obtained the income and expenses over the range:

var monthsToDate = Enumerable.Range(1, 12)
                                .Select(m => new DateTime(DateTime.Today.Year, m, 1))
                                .ToList();

            var sums = from month in monthsToDate
                       select new
                       {
                           month = month,

                           income = (from account in household.Accounts
                                     from transaction in account.Transactions
                                     where transaction.IsIncome && transaction.Created.Month == month.Month
                                     select transaction.Amount).DefaultIfEmpty().Sum(),

                           expense = (from account in household.Accounts
                                      from transaction in account.Transactions
                                      where !transaction.IsIncome && transaction.Created.Month == month.Month
                                      select transaction.Amount).DefaultIfEmpty().Sum(),                                                              
                       };

what I get back is this

.
.
.
[4] = { month = {5/1/2015 12:00:00 AM}, income = 3000, expense = 1804.75 }
[5] = { month = {6/1/2015 12:00:00 AM}, income = 2500, expense = 1560 }
[6] = { month = {7/1/2015 12:00:00 AM}, income = 0, expense = 550 }
.
.
.

Answer 1

You can add this reusable extension method to your code:

internal static class CollectionExtensions
{
    /// <summary>
    /// Returns a sequence whose first element is the first element of the source sequence (if any),
    /// and every subsequent element is the result of applying a specified accumulator function to the
    /// previous element of the resulting sequence and the next member of the source sequence.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="source"></param>
    /// <param name="accumulator"></param>
    /// <returns></returns>
    public static IEnumerable<T> Accumulate<T>(this IEnumerable<T> source, Func<T, T, T> accumulator)
    {
        if (source == null) throw new ArgumentNullException("source");
        if (accumulator == null) throw new ArgumentNullException("accumulator");

        return source.AccumulateImpl(accumulator);
    }

    private static IEnumerable<T> AccumulateImpl<T>(this IEnumerable<T> source, Func<T, T, T> accumulator)
    {
        using (var enumerator = source.GetEnumerator())
        {
            T accumulation;
            T next;

            if (enumerator.MoveNext())
                accumulation = enumerator.Current;
            else yield break;

            yield return accumulation;

            if (enumerator.MoveNext())
                next = enumerator.Current;
            else yield break;

            while (true)
            {
                accumulation = accumulator(accumulation, next);
                yield return accumulation;

                if (enumerator.MoveNext())
                    next = enumerator.Current;
                else yield break;
            }
        }
    }
}

Example usage:

var range = Enumerable.Range(0, 5);                     // 0, 1, 2, 3, 4
var accumulated = range.Accumulate((x, y) => x + y);    // 0, 1, 3, 6, 10

Now, if you change your select to return a named type, instead of an anonymous one (I'm assuming you're using decimal for money - if not, you can adapt this code):

internal class MonthlyIncomeAndExpenses
{
    public DateTime Month { get; set; }
    public decimal Income { get; set; }
    public decimal Expenses { get; set; }
}

var sums = from month in monthsToDate
           select new MonthlyIncomeAndExpenses
           {
               Month = month,
               Income = ...,   // what you already have
               Expense = ...,  // what you already have                                                            
           };

Then you only have to add one easy line:

var accumulated = sums.Accumulate((previous, next) => new MonthlyIncomeAndExpenses
{
    Month = next.Month,
    Income = previous.Income + next.Income,
    Expense = previous.Expense + next.Expense,
});

Answer 2

Since you need an array of arrays for flot, you could just run a loop over your array and sum up the income and expense from all previous month. Something like this (after your existing code):

var flotDataAsList = new List<double[]>();
double balance = 0.0;
for (int i = 0; i <= 12; i++)
{
    DateTime thisMonth = new DateTime(year, i, 1);
    balance += sums.Where(m => m.month == thisMonth).Sum(m => m.income - m.expense);
    flotDataAsList .Add(new double[] { GetJavascriptTimestamp(thisMonth), balance });
}
var flotDataAsArray = flotDataAsList.ToArray();

GetJavascriptTimestamp() method can be taken from flot documentation:

public static int GetJavascriptTimestamp(System.DateTime input)
{
    System.TimeSpan span = new System.TimeSpan(System.DateTime.Parse("1/1/1970").Ticks);
    System.DateTime time = input.Subtract(span);
    return (long)(time.Ticks / 10000);
}