Forum Discussion
Dynamic Transformation to create separate series for charting
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.
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
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
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.
- Thank you for your solution. Greatly appreciated.
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
