Forum Discussion

Pappu1630's avatar
Pappu1630
Copper Contributor
Mar 12, 2022
Solved

Dynamic Transformation to create separate series for charting

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 differe...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Mar 13, 2022

    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's avatar
Pappu1630
Copper Contributor
Mar 13, 2022
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.
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 13, 2022

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.

Resources