Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mueller_82
New Member

visualize given forecast and last year values

Hello,

 

I would like to visualize a line chart with January - december 2017 on x-Axis. I want to visualize actual data of selled amount for the months January and february, for the rest of the months in 2017 I want to use forecast data which I have given in a table by month. As well I want to show the last year values (Jan-Dec 2016) that are given in a seperate table too, but I don't know how to realize it. Is it possible to use the format of the tables for the last year and forecast values? Or do I have to put the forecast data in the format like my actual data so that it can be visualize as one line in the graph?

 

 

My actual data table has the form like the following table: I need it to get the Sum of the selled amount to visualize it. Aim is to visualize the selled amount and to be able to filter it by product Category.

 

Customer IDCategorySelled AmountDateXYXY
1Sweets101.02.2017  
2Vegetables201.03.2017  
3Vegetables101.01.2017  
4Sweets302.02.2017  
5Soup103.02.2017  
6fruits202.02.2017  
7Soup119.01.2017  
8fruits205.01.2017  

 

For the forecast data I have a table in the format like that:

CategoryJanFebMrzAprMaiJunJulAugSepOktNovDez
Sweets10012080859684881001057788100
Vegetables504030252726235040363945
fruits201318283040201015362512
soup302620161215192120304550

 

And the last year values have the same format like the forecast value table:

CategoryJan 16Feb 16Mrz 16Apr 16Mai 16Jun 16Jul 16Aug 16Sep 16Okt 16Nov 16Dez 16
Sweets9018011085100848871105777580
Vegetables494050802786806040364650
fruits307302810040273015362580
soup108060501215401516355090

 

 

I also have a Date table including all dates from 01.01.2016-31.12.2017. I have connected it to the date field of the actual data field.

Can someone help me how I could visualize it? I really don't know how to do it...

 

Many thanks in advance for any tips!

 

Best Regards

 

1 ACCEPTED SOLUTION

Hi @mueller_82,

 

You need to create a date column both in Forecast table and Last Year table. Then create a relationship between the date table with those two tables based on the date column. In Forecast table, create a column to return whether the row is to display in the line chart. Then use this column in Visual level filter pane to filter the line chart. For detail information, see attached .pbix file.

 

q3.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

Hi @mueller_82

 

I suggest you change the format of your forecast table (and the last-year forecast table) to something like this

 

 

Category , Month ,  Value
---------------------------------
Sweets , Jan , 100
Sweets , Feb ,120
Sweets , Mrz , 80

 

You can achieve this using the Unpivot feature in the Query Editor.

 

Once you have the data in this format, it should be easier to build your visual


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

 

thank you very much! I now used the univot feature to change the format of the forecast and the last year table as you suggested (Category, Month, Value for selled amount) .

Im really new in Power Bi and I don't know what the best strategy is to visualize the actual data (Jan 17 and Feb 17) and the forecast data (Mrz17-Dez17) in one line. What measure do I need? Is it better to create one table for that by adding the forecast table to the actual data table, even if the actual data table consists of more columns so that some would be blank?

 

Maybe there is a general procedure how to visualize actual data/forecastdata/Last year data correctly?

 

Thanks!! 🙂

 

Hi @mueller_82,

 

You need to create a date column both in Forecast table and Last Year table. Then create a relationship between the date table with those two tables based on the date column. In Forecast table, create a column to return whether the row is to display in the line chart. Then use this column in Visual level filter pane to filter the line chart. For detail information, see attached .pbix file.

 

q3.PNG

 

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft

wow hank you, that helps me a lot! 

 

Bsst Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.