Actual vs forecast using time intelligence in both rows and columns.

[WCK13]

I am new to Power BI and dicovering DAX is not easy. I need to do a matrix report where the columns are time periods (month/year) and the rows are a combination of monthly forecast for the year and actuals for past months. 

The data comes from 2 different tables. I have a date table and tried to use "userelationship" for the different times but it did not work.

Example: the forecast import in April contains April through December forecast. the January - March numbers on this line should be the actuals for those months. I cannot seem to get the time to use both the rows and columns correctly. 

The example below shows the actuals in yellow. All other numbers are forecast that was imput that month. The totals add both actuals and forecast for the year.

Any help with the dax would be greatly appreciated.

[DataNinja777 · Accepted solution]

Hi @WCK13 ,

To handle a matrix in Power BI that displays actuals for months up to a selected forecast month and forecasts for the remaining months, you'll need a DAX measure that dynamically switches between actuals and forecast values based on time. Assuming you have a date table, and your data model includes an Actuals table with a date column and an Amount column, and a Forecast table with a SnapshotMonth, ForecastMonth, and Amount, you can use a DAX measure like the following.

ActualPlusForecast = 
VAR SelectedMonth = SELECTEDVALUE('Date'[Date])
VAR SnapshotMonth = SELECTEDVALUE('Forecast'[SnapshotMonth])
VAR UseActual = SelectedMonth < EOMONTH(SnapshotMonth, -1) + 1
RETURN
IF(
    UseActual,
    CALCULATE(
        SUM(Actuals[Amount]),
        'Date'[Date] = SelectedMonth
    ),
    CALCULATE(
        SUM(Forecast[Amount]),
        'Forecast'[ForecastMonth] = SelectedMonth,
        'Forecast'[SnapshotMonth] = SnapshotMonth
    )
)

This measure checks whether the currently selected column month is before the forecast snapshot month. If it is, the measure returns the actuals; if it’s equal to or after the snapshot month, it returns the forecast values stored in the snapshot. For correct totals in the matrix visual, wrap this measure inside a SUMX over the distinct column months like this:

ActualPlusForecast_TotalsFixed =
SUMX(
    VALUES('Date'[Date]),
    [ActualPlusForecast]
)

Make sure that your matrix uses 'Forecast'[SnapshotMonth] in the rows and 'Date'[Date] or a formatted version of it (like 'Date'[YearMonth]) in the columns. This structure will allow you to replicate the pattern shown in your example image, where the first few months show actuals and the rest show forecasts entered as of a specific month.

Best regards,

[DataNinja777 · Accepted solution]

Hi @WCK13 ,

To handle a matrix in Power BI that displays actuals for months up to a selected forecast month and forecasts for the remaining months, you'll need a DAX measure that dynamically switches between actuals and forecast values based on time. Assuming you have a date table, and your data model includes an Actuals table with a date column and an Amount column, and a Forecast table with a SnapshotMonth, ForecastMonth, and Amount, you can use a DAX measure like the following.

ActualPlusForecast = 
VAR SelectedMonth = SELECTEDVALUE('Date'[Date])
VAR SnapshotMonth = SELECTEDVALUE('Forecast'[SnapshotMonth])
VAR UseActual = SelectedMonth < EOMONTH(SnapshotMonth, -1) + 1
RETURN
IF(
    UseActual,
    CALCULATE(
        SUM(Actuals[Amount]),
        'Date'[Date] = SelectedMonth
    ),
    CALCULATE(
        SUM(Forecast[Amount]),
        'Forecast'[ForecastMonth] = SelectedMonth,
        'Forecast'[SnapshotMonth] = SnapshotMonth
    )
)

This measure checks whether the currently selected column month is before the forecast snapshot month. If it is, the measure returns the actuals; if it’s equal to or after the snapshot month, it returns the forecast values stored in the snapshot. For correct totals in the matrix visual, wrap this measure inside a SUMX over the distinct column months like this:

ActualPlusForecast_TotalsFixed =
SUMX(
    VALUES('Date'[Date]),
    [ActualPlusForecast]
)

Make sure that your matrix uses 'Forecast'[SnapshotMonth] in the rows and 'Date'[Date] or a formatted version of it (like 'Date'[YearMonth]) in the columns. This structure will allow you to replicate the pattern shown in your example image, where the first few months show actuals and the rest show forecasts entered as of a specific month.

Best regards,

[WCK13]

so much closer but I am not seeing the actuals. The space is blank.

ActualPlusForecast =

VAR SelectedMonth = SELECTEDVALUE('Date'[Date])

VAR SnapshotMonth = SELECTEDVALUE(web_MasterData_Incoming_Combined_Forecasts_History[InsertDate])

VAR UseActual = SelectedMonth < EOMONTH(SnapshotMonth, -1) + 1

RETURN

IF(

    UseActual,

    CALCULATE(

        SUM(Sales_US_Facts_Inv[Sell_Line_Quantity]),

        'Date'[Date] = SelectedMonth

    ),

    CALCULATE(

        SUM(web_MasterData_Incoming_Combined_Forecasts_History[Quantity]),

        web_MasterData_Incoming_Combined_Forecasts_History[Period] = SelectedMonth,

        web_MasterData_Incoming_Combined_Forecasts_History[InsertDate] = SnapshotMonth

    )

)

[V-yubandi-msft · Accepted solution]

Hi @WCK13 ,

1. If the actuals are not displaying, first verify if there is valid data in the Sell_Line_Quantity column of the Sales_US_Facts_Inv table for the selected dates. If there is no data, the measure will return blank values.

2. Break the measure into smaller parts and test each one using simple visuals to identify where the blank values originate. This step will help pinpoint any issues in the logic or data.

3. Once you confirm the data retrieval logic is correct, reapply the measure and check if the actuals display correctly in your report.

This should help resolve the issue effectively.