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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Malsk1_1 ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a calculated column as below

nForecast Spend =
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Month End] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Month End] < EARLIER ( 'Table'[Month End] ) )
    )
VAR _preforecast =
    CALCULATE (
        MAX ( 'Table'[Forecast Spend] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month End] = _predate
                && 'Table'[Forecast Period] = EARLIER ( 'Table'[Forecast Period] )
        )
    )
RETURN
    IF ( ISBLANK ( _predate ), 'Table'[Forecast Spend], _preforecast )

2. Created a clustered column chart

Best Regards

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

Anonymous i think you have got the value coming in right - i will implement it into the bigger environment and come back with any updates.. but in the mean time.. thank you so much!

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

    Anonymous any ideas why it stops working when you add a new month?