October 6, 2017 at 5:15 am
Hi all, hoping some one can help me please with some MDX.
I have the following simplified structure in a cube, we do forecast revisions during each quarter during the year however we only store the revision data for the remaining periods in the year not the preceding ones as by this point we already have the actuals.
So what I want is for when a user selects a Version e.g. '2017 Q3 Forecast' we should return the Q3 and Q4 forecast figures along with the Actuals for Q1 and Q2, I'm trying to avoid replicating the Actuals data as its quite large.
Could someone please provide me with a sample MDX query or point me in the right direction which might achieve this.
Many thanks in advance!
| VersionKey | Version |
| 1 | Actuals 2017 |
| 2 | Forecast 2017 Q1 |
| 3 | Forecast 2017 Q2 |
| 4 | Forecast 2017 Q3 |
| 5 | Forecast 2017 Q4 |
| DateKey | FiscalYear | FiscalQuarter |
| 1 | 2017 | Q1 |
| 2 | 2017 | Q2 |
| 3 | 2017 | Q3 |
| 4 | 2017 | Q4 |
| VersonKey | DateKey | ProductKey | Amount |
| 1 | 1 | 1 | -374 |
| 1 | 2 | 1 | -727 |
| 4 | 3 | 1 | -423 |
| 4 | 4 | 1 | -721 |
| 5 | 4 | 1 | -487 |
October 16, 2017 at 12:42 am
What is your expected output? What tool are you using to generate this output (SSRS, PowerBI, Tableau etc.)? What MDX have you tried already that has not given you the result that you expect?
I suspect that what you are trying to do is possible with a SCOPE statement with one of the time navigation functions but when dealing with SSAS seeing the underlying tables is not espectially helpful. Can you provide a little more information?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply