Change Measure in Matrix based on Month Column (Actual & Forecast)

[dcrow5378]

Hello,

I am trying to create a matrix that will switch based on Month Slicer Selection.  If a month is selected in the slicer, it will display the selected month and the prior 5 months of Actual.  It will also display to the right of selected month, the forecast values for the next 6 months.
Any insights into how to solve this is much appreciated!

Simplified Data Table:

Month Selected in Slicer: Dec-24
Desired Matrix Output:

[DataInsights]

@dcrow5378,

Check out the solution below. Let me know if you encounter any issues adapting it to your model.

https://community.fabric.microsoft.com/t5/Desktop/One-Measure-for-Actuals-Forecast-depending-on-mont... 

[dcrow5378]

Thanks @DataInsights!  Would it be possible to have a a month with two entries like so?

[DataInsights]

@dcrow5378,

Are those partial months in each Jan-25 column? Would the user select a day rather than a month, or is it based on the latest date with actuals (and the remainder is forecast, which means forecast would need to be allocated if it's at the month grain).

[dcrow5378]

It would just be that if Jan-25 was selected from Month slicer then it would show the actual and forecast.  The month is not yet closed for January, so we would need to see actual state as of now and the forecast for the month.  I am only using month in date tables as everything will roll up to the month, no day granularity.
Is that what you are asking?
The forecast data will not change, it is forecasted out for the year, but the January Actual column could change with daily refreshes.

[DataInsights]

@dcrow5378,

We can adjust the DAX to show actuals through the latest date that they exist. Examples using a slicer based on a disconnected MonthYear table (no relationships since that would restrict the matrix to the selected MonthYear):

Select Jan-25. If actuals exist through 2025-01-28, then display actuals through 2025-01-28.

Select Dec-24. If actuals exist through 2024-12-31, then display the full month of actuals.

Displaying Jan-25 as two separate columns in the same matrix (2025-01-01 through 2025-01-28 actuals, and 2025-01-29 through 2025-01-31 forecast) doesn't work in my testing (I'll circle back if I figure out a way).

[dcrow5378]

So this will never be a day situation, always a month rollup.  So if the month selected is current month (January) show all January in Actual, and Forecast is already set for each full month.