Categories
Development

how to get running total by month

I am trying populate a graph and my Model looks like this

public class Transaction : BaseEntity
{
    public TransactionType TransactionType { get; set; }
    public DateTime TransactionDate { get; set; }
    public double TransactionAmount { get; set; }
}
public enum TransactionType{
    Deposit = 0,
    Withdraw = 1
}

i populated this model with sample data of first 2 months of 2018.

    b.HasData(new
            {
                Id = Guid.NewGuid().ToString(),
                AccountId = "37846734-172e-4149-8cec-6f43d1eb3f60",
                TransactionAmount = 3334.38,
                TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 1, 20),
                TransactionType = TransactionType.Deposit
            });
            b.HasData(new
            {
                Id = Guid.NewGuid().ToString(),
                AccountId = "37846734-172e-4149-8cec-6f43d1eb3f60",
                TransactionAmount = -3334.38,
                TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 1, 21),
                TransactionType = TransactionType.Withdraw

            });
            b.HasData(new
            {
                Id = Guid.NewGuid().ToString(),
                AccountId = "37846734-172e-4149-8cec-6f43d1eb3f60",
                TransactionAmount = 1000.23,
                TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 1, 25),
                TransactionType = TransactionType.Deposit
            });
            b.HasData(new
            {
                Id = Guid.NewGuid().ToString(),
                AccountId = "37846734-172e-4149-8cec-6f43d1eb3f60",
                TransactionAmount = 1008.18,
                TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 4, 10),
                TransactionType = TransactionType.Deposit
            });
            b.HasData(new
            {
                Id = Guid.NewGuid().ToString(),
                AccountId = "37846734-172e-4149-8cec-6f43d1eb3f60",
                TransactionAmount = -34.10,
                TransactionDate = new DateTime(DateTime.UtcNow.AddYears(-2).Year, 4, 19),
                TransactionType = TransactionType.Withdraw
            });

I am trying to get a result that what was the balance at end of each month.

for example in

2018 Jan 1000.23
April 1974.23 (1000.23 from January + 974.0 from April)

but what i am getting is

Jan 1000.23
April 974.0

basically it is adding all the values in each month individually.

 var data = from t in transactions
                   group t by new { t.TransactionDate.Year, t.TransactionDate.Month } into g
                   select new
                   {
                       //Balance = g.Select(i =>
                       //{
                       //    currentTotal += g.Sum(x => x.TransactionAmount);
                       //    return new
                       //    {
                       //        //Date = i.TransactionDate,
                       //        //Amount = i.TransactionAmount,
                       //        currentTotal
                       //    };
                       //}),
                       Balance = g.Sum(x => x.TransactionAmount),
                       g.First().TransactionDate.Month,
                       g.First().TransactionDate.Year
                   };
        var yearlyGroups = data.OrderBy(x => x.Month).GroupBy(x => x.Year);
        var lastTwoYearsSortedByMonth = yearlyGroups.OrderBy(x => x.Key).TakeLast(2);

As you can see in the code above i tried something to get the running total. but not working.

Leave a Reply

Your email address will not be published. Required fields are marked *