1 Reply Latest reply on Apr 16, 2018 10:12 AM by Patrick A Van Der Hyde

    Relative Date Filter on Sales vs Target sheet: Not showing data for future months?

    Ben Perlman

      The dashboard has several tables showing sales vs. target for this year. I use a relative date filter so users can select current month, previous date, current quarter, etc.  I have a target spreadsheet that shows account name, month, target.  This is joined to a sql server that shows revenue by day (target spreadsheet goes down to month, revenue server goes down to day).  They are joined at the month granularity (left join to sql server).  All numbers are accurate, except when selecting future dates (for which are not in the sql server as there is no revenue), it just shows the current quarter and current revenue.  If they select "this year", I'd like the target to show the target for the year and where they are at compared to it. 

       

      One of the tables shows the revenue by day, and I really need this to still show revenue by day, but have the reference line that shows target to have the target for whatever is selected (if they select this year, the revenue should just show for what has come in, but the reference line needs to show the total target for the year).

       

      I thought this may be due to it being left joined by the table that has no future dates, but even when I did a full outer join, it had the same limitation. 

       

      I've attached the workbook for reference. The two fields that would need to be updated would be the target reference line, and the target text box at the top so if they select "this year" it would not just show year to date target, but total year.

       

      I've tried outer joins, blends, I can't seem to figure this out