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

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!