Forum Discussion

Malsk1_1's avatar
Malsk1_1
Icon for Helper II rankHelper II
4 years ago

Data Processing for Actual and Forecast data

Hi,   I have the following Actual and Forecasted spend. Month end represents the month end baseline data, planning period represents current and future Actual spend and Forecast spend. Currently ...
Need Help
PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

Please provide sample data (not an image) or a PBIX file (through OneDrive, Google Drive, Dropbox...) and a depiction of the model

Malsk1_1's avatar
Malsk1_1
Icon for Helper II rankHelper II
4 years ago

PaulDBrown  there is a PBIX provided by Anonymous which is a good base for this issue.. it works for whent their are two months, but when i add in March data the previous forecasts default back to 0 even though we have the forecast data for the previous months

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Try:

    Prev Period Forecast =
VAR PP =
    CALCULATE (
        MAX ( 'Table'[Forecast Period] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Forecast Period] <= MAX ( 'Table'[Forecast Period] )
                && NOT ( ISBLANK ( [Sum Forecast Spend] ) )
        )
    )
VAR PF =
    IF (
        ISBLANK ( [Sum Forecast Spend] ),
        CALCULATE (
            [Sum Forecast Spend],
            FILTER ( ALL ( 'Table' ), 'Table'[Forecast Period] = PP )
        ),
        [Sum Forecast Spend]
    )
RETURN
    IF ( ISINSCOPE ( 'Date Table'[Date] ), PF )

    If you need the total:

    Prev Period Forecast with total =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Date Table'[Date],
            'Table'[Forecast Period],
            'Table'[Project]
        ),
        "_Total", [Prev Period Forecast]
    ),
    [_Total]
)

    to get...

     

    I've attached the sample PBIX file

    • Malsk1_1's avatar
      Malsk1_1
      Icon for Helper II rankHelper II
      4 years ago

      PaulDBrown thanks for this, i will test it out with more data and feedback.. once again, thank you!