Excel chart: Dynamically update the relevant range horizontal axis (columns) based on available data

Question

Dear community,

I built a financial model where all relevant data is in one table. An input field for the start and end date in combination with a slicer (that filters row depending on whether the date is in the specified range) allows filtering the shown data accordingly.

However, I have another case I would like to make the shown columns dependent on:

1. If forecast data is used in a chart (either only forecast or forecast & actuals), I would like to show exactly the range as specified above (chart example on the left).
2. If only actual data is used in the chart, I would like the horizontal axis to be limited to the maximum date available in the data (chart example on the right: Aug 23 should be at the very right of the horizontal axis).

I believe that this would be feasible with setting up a second table and a calculated field that additionally includes this as a formula and filters the table range accordingly. However, this would introduce additional complexities (e.g. getting them linked to one slicer) and thus I wanted to ask if you see an option to achieve this when keeping the data in the same table?

Note that I use Mac but if only possible on Windows, I can also get access to a Windows laptop.

Many thanks!

Answer 1

Could you share a test workbook with some dummy data to test behavior? Then we may give suggestion on it.

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.