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
Malsk1_1's avatar
Malsk1_1
Icon for Helper II rankHelper II
4 years ago

any help would be appreciated.. 

  • 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