Filter Table Using Value Selected In Excel Pivot Table Connected to SSAS Cube

Question

I am trying to combine two tables in an SSAS cube and filter one of the tables depending on a value selected by the user in a pivot table from an Excel file connected to the cube. Is this possible and if so how do I do it?

To describe in more detail, I have two tables: history and forecast. For history there is only one set of records for each time period, but there are many forecasts for most periods. When a user selects (in the Excel pivot) a particular forecast version to view, I want to show them all of the forecast records available for that forecast and also history but only for periods that preceded the forecast.

So for example if a user today selects (in the Excel pivot) forecast 1, produced in 2021 Q3, the following is where the records come from:

• 2021 Q1 - history
• 2021 Q2 - history
• 2021 Q3 - forecast
• 2021 Q4 - forecast
• 2022 Q1 - forecast
• 2022 Q2 - forecast

but if a user today selects forecast 2, produced in 2022 Q1, the following is where the records come from:

• 2021 Q1 - history
• 2021 Q2 - history
• 2021 Q3 - history
• 2021 Q4 - history
• 2022 Q1 - forecast
• 2022 Q2 - forecast

Hopefully its clear what I'm trying to accomplish. If this approach won't work but there is another way to accomplish this, please let me know.

Thank you in advance for your help!

Answer 1

Create a new table that is unconnected in the model for the filter that lists your forecast periods. Mine here has a new forecast every quarter.

The start date is the column used to determine whether to use History or Forecast in these DAX measures:

Actual Value = IF(HASONEVALUE('Filter'[Period]),
                  CALCULATE( SUM(History[Value]), History[Date] < MAX('Filter'[Start date]) ),
                  "Select quarter")
Forecast Value = IF(HASONEVALUE('Filter'[Period]),
                  CALCULATE( SUM(Forecast[Forecast]), Forecast[Date] >= MAX('Filter'[Start date]) ),
                  "Select quarter")
Actual or Forecast Value = IF(HASONEVALUE('Filter'[Period]),
                  [Actual Value] + [Forecast Value],
                  "Select quarter")

And here's what it looks like in Excel: