Slicer selection dynamically returns another row

ertainly, you can achieve this using DAX measures in Power BI. You'll need to create a measure that considers both the selected value in the slicer and the corresponding YYYY00 row. Here's a general outline of how you could approach this:

Assuming you have a column named 'Date' and another column named 'Value' in your table, and the slicer is based on the 'Date' column:

1. Create a Measure for Forecast Values:

ForecastValue = CALCULATE(SUM(Table[Value]), RIGHT(Table[Date], 2) <> "00")

1. This measure calculates the sum of 'Value' only for rows where the last two characters of the 'Date' column are not "00". This filters out the actuals (YYYY00).

2. Create a Measure for Actual Values:

ActualValue = CALCULATE(SUM(Table[Value]), RIGHT(Table[Date], 2) = "00")

1. This measure calculates the sum of 'Value' only for rows where the last two characters of the 'Date' column are "00". This filters out the forecasts (YYYY01-YYYY12).

2. Create a Combined Measure:

ActualValue = CALCULATE(SUM(Table[Value]), RIGHT(Table[Date], 2) = "00")

1. This measure calculates the sum of 'Value' only for rows where the last two characters of the 'Date' column are "00". This filters out the forecasts (YYYY01-YYYY12).

2. Create a Combined Measure:

CombinedValue = [ActualValue] + [ForecastValue]

1. This measure sums up the values calculated for actuals and forecasts.

Now, use the 'CombinedValue' measure in your visualizations. When you select a value in the slicer corresponding to YYYY01-YYYY12, it will show the combined value of actuals (YYYY00) and forecasts.

Make sure to replace 'Table' with the actual name of your table in these measures. Adjust the column names accordingly if they are different in your dataset.

This approach doesn't modify the data structure and should provide the desired result based on your requirements.