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  - So the solution works for when there is data for Jan and Feb, however when i populate data for March onwards it reverts the previous forecasts to 0 - see images below:

 

If you review my dropbox file i have created a new tab which shows i pivoted chart and how it should be represented - your solution is close as it works for Jan -> Feb but then it stops working, as in the previous forecasts are wiped..  appreciate if you can have a look - thanks.

 

The numbers are different from the charts i have shown here versus the sample data provided in the dropbox file