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.
- Thank you & Riny. All my data points (forecast & actual) are coming from the Analysis Services. The format I'm getting is as shown in the screen shot (Input) . My preference is do the transformation in PQ(M) or DAX. I'm thinking of using some sort last value function in actual column and once I obtain that position of the cell, move over to the next column to get to get the forecast data till June for any given Financial Year. Every month this process continues.
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.
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