6 Replies Latest reply on Mar 10, 2016 9:51 AM by Jessica Oong

    How to look only at the last row of data to populate a table/data sheet?

    Jessica Oong

      Hi! I have an excel spreadsheet with various cash values over a period of time. I'd like to create a dashboard that highlights the most recent numbers, and I want this dashboard to update automatically as we add additional metrics each month. Another challenge, I want to create 2 separate columns of related date. For example, this is what I'd like my dashboard to show:

       

      Last Month's MetricsBudgetedActual
      Total Cash Receipts
      Total Cash Receipts (A+B)
      Cash Receipts A

      Cash Receipts B

      Cash Receipts C

       

      Is there a calculation I can create on the Month-Year measure to use that as a trigger so the dashboard only pulls from that row? Or any other suggestions?

       

      Here is a sample data set:

       

      Month-YearTotal Cash Receipts

      Total Cash Receipts

      Budget

      Total Cash Receipts (A+B)Total Cash Receipts (A+B) BudgetCash Receipts ACash Receipts A Budget
      Cash Receipts BCash Receipts B Budget
      Cash Receipts CCash Receipts C Budget

      2015-09

      10000001000000900000900000800000800000100000100000100000100000
      2015-101120000115000010200001050000875000900000145000150000100000100000
      2015-1112000001300000110000012000009250001000000175000200000100000100000
      2015-12160000013000001500000120000012500001000000500000200000100000100000

       

      I want to use the last row's date (i.e. 2015-12) to populate the table, so right now that means my dashboard should show:

       

      Last Month's MetricsBudgetedActual
      Total Cash Receipts13000001600000
      Total Cash Receipts (A+B)12000001500000
      Cash Receipts A10000001250000

      Cash Receipts B

      200000500000
      Cash Receipts C100000100000

       

      But next month, I'd want it to automatically read 2016-01.

       

      Any help is much appreciated.