Forum Discussion

benaford's avatar
benaford
Frequent Visitor
4 years ago
Solved

Can't get chart to plot forecast

I'm trying to create a chart that compares current units sold by week to forecasted units across the same time and then plots the forecast out for the rest of the year. However, I have three tables -...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

benaford's avatar
benaford
Frequent Visitor
4 years ago

Amazing job! Thank you! Can I just ask what purpose the Dim Item Table serves in this solution? 

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

Hi benaford 

 

The Dim Item table is used to filter both Forecast and Sales tables. The relationship between Forecast and Sales has been modified, so the Forecast (or Sales) table cannot filter the other table using its Item Name column correctly. Using a Dim table can avoid a scenario that when one item exists in Forecast (or Sales) but doesn't exist in the other table, which would cause data missing in a visual. There is a concept of modeling -- Start schema. You can refer to Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD 

 

Best Regards,
Community Support Team _ Jing