Forum Discussion
Can't get chart to plot forecast
Hi benaford
I made several changes to your pbix. Please download the attachment at bottom to see details. Changes are:
1. Remove the old relationship between Sales and Forecast tables. Add a one-to-many relationship from Calendar to Forecast. The old relationship uses Sales table to filter Forecast, which caused the data missing as Sales table doesn't have data past week 22. Using Calendar table to filter Forecast can avoid this.
2. Add a Dim Item table to the model and create one-to-many relationships from it to both Sales and Forecast tables on Item Nbr or Item Name columns. Use DimItem's Item Name in the slicer on report.
3. Remove FISCAL YR from the visual. Then you will have the following visual as expected.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Community SupportHi benaford
Your current Forecast table only has forecast units till week 202222. To display forecast unit values later than week 202222, you need to have those values in the table. It cannot display nonexistent data.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Jing - that makes a lot of sense, thank you! However, even adding forecast for weeks 23-52, I still can't get the graph to plot past week 22:
Here's a glimpse of my forecast table weeks:
What is going on here?
https://1drv.ms/u/s!Aq5qm0GeT2XSgqdgqIj3GwPOcKOL7w?e=Tq7mRN
grantsambornSolution Sage
When I removed the relationship between Sales and Forecast and replaced it with a one-to-many between Calendar[WEEK] and Forecast[WEEK], it seems to work.
- v-jingzhang
Hi benaford
I made several changes to your pbix. Please download the attachment at bottom to see details. Changes are:
1. Remove the old relationship between Sales and Forecast tables. Add a one-to-many relationship from Calendar to Forecast. The old relationship uses Sales table to filter Forecast, which caused the data missing as Sales table doesn't have data past week 22. Using Calendar table to filter Forecast can avoid this.
2. Add a Dim Item table to the model and create one-to-many relationships from it to both Sales and Forecast tables on Item Nbr or Item Name columns. Use DimItem's Item Name in the slicer on report.
3. Remove FISCAL YR from the visual. Then you will have the following visual as expected.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.