Here are some initial thoughts on the supplied PBIX
- Your Date table wasn’t marked as a date table and you were leveraging of the hidden date table in a calculated column.
- There was no relationship between Product and Forecast, as a consequence you hadn’t used the Product Item code in your table visual but the Forecast Item code. I strongly suggest hiding all keys on the Fact table side to avoid these kind of selection errors.
- Your Date dimension table doesn’t have sufficient detail for what you are trying to achieve. For example it doesn’t include a single dimension at the Week level, that you intent to report on…
- I suspect you want to use the Timeinterval column as your Week ID within the allocation table but as mentioned above, currently there are no dimensions at the week level present in your date table. You need at least one to match this.
Note that there’s no Year ID tied to the Timeinterval so without an active filter on the Year all Week IDs across all selected years could be seen as valid.
When a common dimension for Weeks is introduced/present in both Date- and Allocation table, you should create a relationship virtually with TREATAS because the granularity in the Date table should be at the daily level while in the Allocation table it’s at the weekly level. You will find many excellent resources within both the portal and the forum on how to do just that.
But first, before you write any DAX, your model needs to be set up correctly, please review the Advanced data transformations and modelling course within the portal.
I hope this is helpful.