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

Community 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...