I have a table (datasource 1) with revenue by day, by account. This is joined to another spreadsheet (datasource 2) with targets, by month, by account.
datasource 1 is only filled out until the most recent day there is revenue on an account (so no future dates).
datasource 2 has targets for the entire year
I am trying to graph a revenue vs target, and allow users to select a relative date (current month, previous month, current quarter, current year).
If I use the data from datasource 1, i can show a running total of the daily revenue with a line of what the target is:
However, if I filter to "current year", or "current quarter", since they include future dates, it will only show the target line for dates there is revenue for (so won't add in the target for future dates).
What would be the best way to use the monthly date from targets so it can include future months, but also be able to bring in the DAILY revenue to date, for whatever period is selected?
would this be a dateadd to get a daily granularity of the targets? I'm not doing this correctly if so.
should I do an outerjoin to get dates from both, and use an LOD calc to get the max total target for the dates selected?