Graphing Cumulative Sales vs. Projections
I would like to show our cumulative sales over the course of the fiscal year against a projection for the full year. I've created a Query Table against two tables with a LEFT JOIN on the date: (1) a projections table with 366 rows (one for each day) and columns for the sales projection for that day and a running total projection. (2) a sales table, showing total sales for each day. The table looks as I would expect, with all dates having a projected sales value but many dates have empty sales.
The problem is that when I chart the sales against the projection by week, I get a cumulative line to the current week and then it flattens out through the end of the fiscal year at whatever today's cumulative sales value is. How do I structure the data table or modify the chart so that it shows the projection through the end of the fiscal year but only shows the sales data through the current week?