Hi,
I have a list of activities with their monthly planned and actual values.
I'm trying to analyze the actual performance against the plan by plotting them against each other.
The catch is that distribution for planned value for next month should start only after distribution of values for first month are completed.
For e.g. in case of Activity 2-ABC ( details in table), Plan for Jan and Feb are 50 each, Actuals for Jan, Feb are 10 & 90 respectively.
So 50 out of actuals should be plotted under Jan ( until reaches planned value of 50-Jan) and remaining 50 to Feb.
Desired Result:
Arrangement of columns/rows are flexible and can be modified, also open to Power Query/Pivot table solutions.
I have a list of activities with their monthly planned and actual values.
I'm trying to analyze the actual performance against the plan by plotting them against each other.
The catch is that distribution for planned value for next month should start only after distribution of values for first month are completed.
For e.g. in case of Activity 2-ABC ( details in table), Plan for Jan and Feb are 50 each, Actuals for Jan, Feb are 10 & 90 respectively.
So 50 out of actuals should be plotted under Jan ( until reaches planned value of 50-Jan) and remaining 50 to Feb.
| Activity | Company | Month | Plan | Actual |
| Activity 1 | ABC | Jan-23 | 100 | 30 |
| Activity 2 | ABC | Jan-23 | 50 | 10 |
| Activity 1 | DEF | Jan-23 | 200 | 50 |
| Activity 1 | ABC | Feb-23 | 0 | 50 |
| Activity 1 | ABC | Mar-23 | 0 | 20 |
| Activity 1 | DEF | Feb-23 | 0 | 150 |
| Activity 2 | ABC | Feb-23 | 50 | 90 |
Desired Result:
| Month | Plan | Jan-23 | Feb-23 | Mar-23 |
| Jan-23 | 350 | 90 | 240 | 20 |
| Feb-23 | 50 | 50 |
Arrangement of columns/rows are flexible and can be modified, also open to Power Query/Pivot table solutions.
