Actuals and Forecast are not populating into the Matrix Table Correctly

[BlueWhite3699]

Hi Experts

Sample File: https://www.dropbox.com/scl/fi/7r5qt4xlaodzs4584xzsx/Sample_Modified_.pbix?rlkey=kpe9x1fuohk0mdoqyc3... 

The Measure is not working as apected...The measure is in the Measure Table and in the Matrix Visual too.

FY - Year runs from April to March

My issue is with step 3. I want to be able to show the Actauls for April and plus 11 months forecast in the same DAX measue

When Forecast - P01 is selected from the slicer

When Forecast - P02 is selected from the slicer i want to be ableo show the Actauls for April & May and plus 10 months forecast in the same DAX measue

This ios where i am stuck

[BlueWhite3699]

Hi Rohit thanks for the feedback - but this is slightly off. If you look at Forecast - P01 only April should have Actuals values and the rest of the periods should be Forecast values - see table below.

The for Forecast - P02 only actuals for Apr and May and then 10 month of Forecast Values. and so on

[BlueWhite3699]

cannot get this over the line - HELP please

[rohit1991]

Hi @BlueWhite3699 ,

It looks like your DAX measure needs to dynamically adjust actuals and forecasts based on the selected forecast period while aligning with your April-to-March fiscal year. To achieve this, you can use SELECTEDVALUE() to capture the forecast period (e.g., P01, P02), then calculate the number of actual months accordingly. A combination of IF(), SWITCH(), and DATESINPERIOD() can help determine whether a given month should display actuals or forecasts. Essentially, the measure should check if a month's position is within the actual range and return actual values, otherwise, return forecast.

[BlueWhite3699]

hi rohit spot on but i am struggling here.

[rohit1991 · Accepted solution]

Hi @BlueWhite3699