Mar 12 2022 02:04 PM
Hi : My data source provides me the data in the following format. I need to do some transformation to develop a line chart with 2 series (representing each financial year). Also I need to use different colors for actual and forecast data. Forecast data starts from the month, which is yet to be completed e.g March till June (end of fiscal year)
I want to build something similar but not sure how to show the forecast data points in different color (differentiating from actual data points). Any help would be greatly appreciated. Thanks
Mar 12 2022 02:38 PM
@Pappu1630 You would need to move the Forecast data into the next column and expand the range of data to be processed to include that column. You would then get
Mar 12 2022 03:33 PM
Thanks for your reply. What sort of functions I can apply either in PQ or excel to shape the data into something like below from the data source (input data). When every month finishes I need to pick up forecast value same as the actual value for that period. As shown below I dont need any forecast value prior to last current month that just ended. Regards
Mar 12 2022 05:23 PM
@Pappu1630 If you use the formula shown in the following screen shot, you will get the following with the forecast line running along the zero line until it jumps up to the value for the most recent actual value.
I think that the only way to avoid that is to manually delete the formulae from the range shown selected
Mar 12 2022 08:35 PM
Solution@Pappu1630 The attached file contains an example of what I believe is what you asked for.
You would need an original Forecast (or Budget) column to begin with. One that might change while the year progresses (like column FC in the picture below). Then, another column contains the Actuals and yet another column calculates the Forecast for the remainder of the year. The formula is shown in the picture, cell D2. Returning NA() for the months that have an actual (except the last one) will cause these data points to be regarded as "empty cells" and, by default, be displayed as gaps. That is, they will not be connected with lines.
Mar 12 2022 08:56 PM
Mar 12 2022 10:38 PM
@Pappu1630 Have attached a few more options. On with a regular pivot table and pivot chart directly on the data set, playing with the line formatting to segregate the actuals from the forecast.
Another example used PQ to add a column for the forecast numbers only. Then, load to the DM and create a pivot chart.
Mar 14 2022 04:46 AM
Mar 12 2022 08:35 PM
Solution@Pappu1630 The attached file contains an example of what I believe is what you asked for.
You would need an original Forecast (or Budget) column to begin with. One that might change while the year progresses (like column FC in the picture below). Then, another column contains the Actuals and yet another column calculates the Forecast for the remainder of the year. The formula is shown in the picture, cell D2. Returning NA() for the months that have an actual (except the last one) will cause these data points to be regarded as "empty cells" and, by default, be displayed as gaps. That is, they will not be connected with lines.