Mapping Data into a Dimension
For a UX Dashboard, I'm trying to have some functionality where the end user can choose a year and a version and it would then populate that data from Jan-Dec in front of them. I'd ideally like them to be able to then choose another year and version and to have both compare to each other.
For example, if they were to pick Budget 2020 and Actuals 2019, it would show all the Jan-Dec data for Budget 2020, all the data for Actuals 2019 below it (perhaps in another grid) and then a grid with the variances.
To do this, I thought about having a module where they could have a dropdown to choose these 4 variables (Year 1 Version 1, Year 2 Version 2) and to then have some form of lookup, i.e. if 2020 is chosen, lookup Jan 20 and return in the Jan column. Does anyone have an idea of how this could work and what formulas I could use? I'm struggling to take the data (i.e. Jan 20) and to have it lookup into a dimension on the module where the actual data is stored.