SOLVED

Dynamic Transformation to create separate series for charting

Copper Contributor

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)

Pappu1630_0-1647121883060.png

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

Pappu1630_2-1647122479625.png

 

 

 

7 Replies

@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 

Doug_Robbins_Word_MVP_0-1647124685151.png

 

@Doug_Robbins_Word_MVP 

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_0-1647127575575.png

Pappu1630_1-1647127712648.png

 

@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.

Doug_Robbins_Word_MVP_0-1647134429899.png

I think that the only way to avoid that is to manually delete the formulae from the range shown selected

Doug_Robbins_Word_MVP_1-1647134566086.png

 

 

 

 

best response confirmed by Pappu1630 (Copper Contributor)
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.

Screenshot 2022-03-13 at 05.21.42.png

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.

Thank you for your solution. Greatly appreciated.
1 best response

Accepted Solutions
best response confirmed by Pappu1630 (Copper Contributor)
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.

Screenshot 2022-03-13 at 05.21.42.png

View solution in original post