powerbidaxpowerbi-desktopforecastingforecast

How attach actuals and forecast in a Line Chart

I have 2 problems with my forecast. My forecast is this:

2025 & Forecast = 
VAR CurrentMonth = MAX('Calendar'[Month Number])

-- Actual 2025 value
VAR Actual2025 = CALCULATE(
    SUM(Forecast[Costs]),
    'Calendar'[Year] = 2025,
    'Calendar'[Month Number] = CurrentMonth
)

-- Forecasted value for 2025 (reuse the existing Forecast measure)
VAR Forecasted2025 = [Forecast Engine]

-- Total logic
VAR TotalMerged =
    IF(
        ISINSCOPE('Calendar'[Month Number]),
        COALESCE(Actual2025, Forecasted2025),
        SUMX(
            VALUES('Calendar'[Month Number]),
            VAR _Month = [Month Number] 
            VAR _Actual2025 = CALCULATE(SUM(Forecast[Costs]), 'Calendar'[Year] = 2025, 'Calendar'[Month Number] = _Month)

            -- Use the forecast measure instead of recalculating
            VAR _Forecast = CALCULATE([Forecast Engine], 'Calendar'[Month Number] = _Month)

            RETURN COALESCE(_Actual2025, _Forecast)
        )
    )

RETURN TotalMerged

Problem One: I'm trying to put the actuals + forecast in a matrix and they look pretty good. Except for the current month! The curent month is so low because it's showing only the actuals but no the forecasts. That's why it's so low. As per today ( 12/04/2025) It's showing only the actualist form April 1th to April 12th. The actual is not counted in the matrix. Why?

enter image description here

Problem Two: Down below I'm adding a Line Chart hoping to project my forecast till the end of the year. But it seems like my forecast is in the past!!! It's forecasted in 2024? That is very bad for a forecast :(

enter image description here

I've followed standard procedures to create the calendar so I'm quite confident that the calendar might be implicated but it's not the real root cause:

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1,
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Day", DAY ( [Date] )
)

If you want to help me you can download:

Thank you so much for your help.

Solution

  • Your main issue is that your Forecast Engine measure is based on Month grain.

    Try the following:

    1. Create an Actual measure
    Actual = SUM(Forecast[Costs])
    1. Create a Forecast measure
    Forecast = 
  SUMX(
    DISTINCT('Calendar'[Date]),
    var rollingAvgDays = -7
    var thisDate = MAX('Calendar'[Date])

    var y0 =
      AVERAGEX(
        DATESINPERIOD( 'Calendar'[Date], thisDate, rollingAvgDays, DAY),
        [Actual]
      )
    var y1 = 
      AVERAGEX(
        DATEADD( DATESINPERIOD( 'Calendar'[Date], thisDate, rollingAvgDays, DAY), -1, YEAR),
        [Actual]
      )
    var y2 = 
      AVERAGEX(
        DATEADD( DATESINPERIOD( 'Calendar'[Date], thisDate, rollingAvgDays, DAY), -2, YEAR),
        [Actual]
      )

    var result = DIVIDE(y2 + y1 + y0, 3)
    var maxActualDate = CALCULATE( MAX(Forecast[date]), ALL(Forecast) )
    return IF(thisDate > maxActualDate, result)
    // return result, use this to see the historic forecast on a chart for tuning
  )

    This forecast measure takes the rolling 7 day average of the current date and the previous two years and averages those. Additionally, it will only return a value for dates greater than that available. Change the return line to return result if you'd like to see it historically on a chart.

    For example, with return result, you can see it is a pretty good match in this zone (where there is 3 yrs of data).

    Historic forecast

    Do change the return back to return IF(thisDate > maxActualDate, result).

    Finally, update your 2025 & Forecast measure to

    2025 & Forecast = 
  CALCULATE(
    [Actual] + [Forecast],
    KEEPFILTERS('Calendar'[Year] = 2025)
  )

    Results:
    Matrix

    Chart