Currently, I have a database that is updated everyday to accumulate data for yesterday. One of the metric is the spend on a particular category in a particular channel. I have made a simple mock up of data in a data.csv file as of now.
There is other Budget_test.csv file that includes the budget for a particular category at the channel level and the date range between which the category is currently running. Also it includes if that category is currently Live or not. A category can run multiple times in a year for a short period of time. I can modify this csv file and add or delete any columns as per the requirements.
I want to produce a graph to show the pacing i.e. the amount spent from the budget till now from the date the category started running and the percentage with which we will overspend or underspend the budget at the end day of the category run. It should also show the total budget, budget till date, amount spent between the period for all the live categories.
I have attached the sample workbook with the calculations and the graph.
The problem I am facing is that I can't get the sum of spend for the category at the channel level, only for the date range from the day it started till yesterday. It sums the spend for the whole year, which also changes the pacing percentage. How can I create a calculated field or some parameters so that it only shows the amount spent between the start and end date of the category.