actuals vs. forecast calculate difference between two tables

[casamontagna]

Dear community,

i am rather new to PowerBi and data analysis, thus i hope my question is not considred as too stupid..

i have a table containing all sales actuals of 2018 and 2019 and another table whichn is containing the forecasted values of 2020. i need to visualize the change YoY between 2018 and 2019 (actuals vs. actuals) and between 2019 and 2020 (actuals vs. forecast) if possible in one visualv(shown in red on the screenshot). I only need the date to distinguish between actuals and forecast; so i built a sum for the total sales of 2018 and 2019 and a sum of the total forecasted values in 2020.

now i am struggling in visualizing the differences ... appreciate if somebody could help me out.

[FilipFireFlyBI · Accepted solution]

Hi,

When you create one table containing both actuals and forecast you can combine this. Both 2 tables and are fact tables, so here you prefer to create 1 new/combined fact table. Creating one Combined table can be done via Append in the Query Editor.

See this link for PBI Desktop file.

[v-xicai]

Hi   @casamontagna ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.
Thanks

[v-xicai]

Hi @casamontagna ,

You may create calendar table first of all, create relationship between calendar table and the other two tables on date field.

Calendar =CALENDARAUTO() .

Then you may put the Calendar[Date] into Axis box of Line chart, put [total sum from actuals table] and [total sum from forecast table] into Value box.

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.