Can't get chart to plot forecast

[benaford]

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 - Sales, Forecast and Calendar. When I try to plot current year the forecast will only plot out as far as the current week of sales, but not any further. 

This is last year - it will force the forecast line to plot across all 52 weeks:

This is current year - forecast will only plot out to the last week where there are sales recorded:

I feel like this has something to do with the calendar and the filters, but I can't figure it out. I'm a newbie to this, so I apologize in advance is this is something super simple that I'm not aware of.

TIA ~

https://1drv.ms/u/s!Aq5qm0GeT2XSgqdfVf9AT4fXWYm28A?e=0JOoDh

[benaford]

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

[v-jingzhang]

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

[benaford]

I still haven't solved the problem, even after updating the calendar table. Any suggestions are welcome...

[v-jingzhang]

Hi @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.

[benaford]

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

[v-jingzhang · Accepted solution]

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.

[grantsamborn]

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. 

[benaford]

Oh yes. I had my calendar offset by a year. Ok, I fixed that and it's still not plotting the forecast by week out past the last week of current year. Not sure if I was clear in my original post, but here's what I'm wanting PBI to do:

I fixed the calendar issue and updated my pbix file here: Sample PBIX file 

[amitchandak]

@benaford , Check your calendar table , it need to have future date