ASP.Net MVC - Razor Displaying account totals for customers by Month/Year Columns
<p><a href="https://i.stack.imgur.com/NUoTg.png" rel="nofollow noreferrer"></a><a href="https://i.stack.imgur.com/iqdxG.png" rel="nofollow noreferrer"></a>I have a statement summary view that i need to show total balance on a customers account by month and year.</p>

<p>I have created the linq query and view models which successfully pull this data from the database.</p>

<p>I want to display this data in a tabular form e.g.</p>

<p><strong>View Desired</strong></p>

<p><a href="https://i.stack.imgur.com/Z4oj2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iqdxG.png" alt="enter image description here"></a>
<br /><br />
My LINQ code</p>

<pre><code>var monthlyTotals = from t in db.InvoiceItems.AsEnumerable()
                                 // where t.Invoice.LegalFile.IsClosed == false
                                  group t by t.Invoice.LegalFile.Client into g
                                  select new StatementSummaryVM
                                  {
                                      Client = g.Key,
                                      GrandTotal = g.Sum(x =&gt; x.AmountInclVAT),
                                      MonthlyTotals = from i in g
                                              group i by new
                                                    {
                                                        month = i.ItemDate.Month,
                                                        year = i.ItemDate.Year
                                                    }
                                              into d
                                              select new MonthlyTotalsVM
                                              {
                                                  Date = new DateTime(d.Key.year, d.Key.month,1),
                                                  Amount = d.Sum(s =&gt; s.AmountInclVAT)
                                              }
                                  };

            return monthlyTotals;

        }
</code></pre>

<p>My View Model</p>

<pre><code>public class StatementSummaryVM
{
    [Display(Name = "File No.")]
    public int Id { get; set; }

    public Client Client { get; set; }
    public IEnumerable&lt;MonthlyTotalsVM&gt; MonthlyTotals { get; set; }
    [Display(Name = "Grand Total")]
    [DisplayFormat(DataFormatString = "{0:C}")]
    public decimal GrandTotal { get; set; }
}

public class MonthlyTotalsVM
{
    [DisplayFormat(DataFormatString = "{0:MMM-yyyy}")]
    public DateTime Date { get; set; }
    [DisplayFormat(DataFormatString = "{0:C}")]
    public decimal Amount { get; set; }
}
</code></pre>

<p>My Current View Code</p>

<pre><code>&lt;table class="table"&gt;
    &lt;tr&gt;
        &lt;th&gt;File No.&lt;/th&gt;
        &lt;th&gt;Client&lt;/th&gt;
        &lt;th&gt;Grand Total&lt;/th&gt;

        @foreach (var item in Model)
        {
            &lt;th&gt;&lt;/th&gt;
            foreach (var monthlyTotal in item.MonthlyTotals)
            {
                &lt;th&gt;@Html.DisplayFor(model =&gt; monthlyTotal.Date)&lt;/th&gt;
            }
        }
    &lt;/tr&gt;

    @foreach (var item in Model)
            {
        &lt;tr&gt;
            &lt;td&gt;@item.Client.Id&lt;/td&gt;
             &lt;td&gt;@item.Client.Name &lt;/td&gt;
             &lt;td&gt;@item.GrandTotal&lt;/td&gt;

        @foreach (var monthlyTotal in item.MonthlyTotals)
        {
             &lt;td&gt;@Html.DisplayFor(model =&gt; monthlyTotal.Date)&lt;/td&gt;
            &lt;td&gt;@monthlyTotal.Amount&lt;/td&gt;
        }
        &lt;/tr&gt;
    }

&lt;/table&gt;
</code></pre>

<p>Which looks like this when rendered</p>

<p><strong>Current Rendered View</strong><a href="https://i.stack.imgur.com/Z4oj2.png" rel="nofollow noreferrer">1</a>:<a href="https://i.stack.imgur.com/8rBJq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NUoTg.png" alt="enter image description here"></a></p>

<p>Im struggling to get this to display correctly. </p>

<p>Any help would really be appreciated.Thanks</p>