I was able to solve this particular challenge. Here is how I did it....
First task is to build a filter module where I run through the various IF THEN ELSE statements to create a grid which would allow me to determine which version over which time period I pull through to my working chart module.
There a three possible combinations of version and time that need to be taken into account;
Actual version for actual time
Non Actual versions for future time
Non Actual versions for current period
These formulas produce the grid which I can use in all future chart modules to determine which values need to be calculated for which version and time period.
I can now use this grid to inform my IF THEN ELSE statement in the chart module. In this example I am using a line item subset from the target module and collect() to pull that data in and the filter module to determine which combination of version and time are valid.
The Divide(0,0) closing out the query is used to eliminate all zeros which when left in the data causes the zero data points to be visible for forecast and budget versions.
The final outcome -
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA