Dynamic Chart to Change ranges with additional dates entered in the data set

Hi All!

I am currently building a Dashboard and I've been trying to create a chart in which the range is controlled by a dropdown. That is, I have sales data spanning from week 1 to week 10 and every week I will continue to add weekly data into the dataset, therefore next week the data set will be from week 1 to week 11. The chart I have is a simple bar chart and I would like to (1) have it update automatically when I add new weekly data and (2) be able to select on the dashboard up to what week I want to see. I have a dropdown list with the weeks.

I would ideally select the week in the dropdown list and it would change the range of the chart to take into consideration up to what week I selected. For example if I select week 7 in the list the range of the chart would adjust to be from weeks 1 to 7.

I know that might be too complicated so I would like to at least have the chart update automatically once I have entered a new week of data. I've tried using Offset to have a dynamic range that would consider the last week of data and change the range but have had no luck so far.

I uploaded a simple excel template of how my data is organized, it more complicated than that but that's the idea of it.

Thanks so much!