Data Processing for Actual and Forecast data

[Malsk1_1]

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 for Feb, you can see that the Actual spend is being populated, however the Forecast spend is being missed, although I can get the forecast spend from the previous month end (should be 60).. is there any logicial methid i can use in Power BI directly to popualte the previous forecast from the previous month end?

Month End	Forecast Period	Actual Spend	Forecast Spend
Jan-21	Jan-21	100
Jan-21	Feb-21	0	60
Jan-21	Mar-21	0	70
Jan-21	Apr-21	0	80
Jan-21	May-21	0	90
Jan-21	Jun-21	0	100
Jan-21	Jul-21	0	110
Jan-21	Aug-21	0	120
Jan-21	Sep-21	0	130
Jan-21	Oct-21	0	140
Jan-21	Nov-21	0	150
Jan-21	Dec-21	0	160
Feb-21	Jan-21	100
Feb-21	Feb-21	150
Feb-21	Mar-21	0	70
Feb-21	Apr-21	0	80
Feb-21	May-21	0	90
Feb-21	Jun-21	0	100
Feb-21	Jul-21	0	110
Feb-21	Aug-21	0	120
Feb-21	Sep-21	0	130
Feb-21	Oct-21	0	140
Feb-21	Nov-21	0	150
Feb-21	Dec-21	0	160

So what i would like to see on the table is as follows - the Forecast is being populated from the previous months data (60):

Month End	Forecast Period	Actual Spend	Forecast Spend
Jan-21	Jan-21	100
Jan-21	Feb-21	0	60
Jan-21	Mar-21	0	70
Jan-21	Apr-21	0	80
Jan-21	May-21	0	90
Jan-21	Jun-21	0	100
Jan-21	Jul-21	0	110
Jan-21	Aug-21	0	120
Jan-21	Sep-21	0	130
Jan-21	Oct-21	0	140
Jan-21	Nov-21	0	150
Jan-21	Dec-21	0	160
Feb-21	Jan-21	100
Feb-21	Feb-21	150	60
Feb-21	Mar-21	0	70
Feb-21	Apr-21	0	80
Feb-21	May-21	0	90
Feb-21	Jun-21	0	100
Feb-21	Jul-21	0	110
Feb-21	Aug-21	0	120
Feb-21	Sep-21	0	130
Feb-21	Oct-21	0	140
Feb-21	Nov-21	0	150
Feb-21	Dec-21	0	160

Any help would be appreciated as this would then allow me to create a Actual v Forecast chart for current and future months.

[Malsk1_1]

@PaulDBrown see file located in the share -->  

Power Data Processing

The model is as follows:

Date table is connected to the Month End

I have a Date filter using the Date table to filter out the Month End

I have created the charts i would like to see..

[v-yiruan-msft]

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]

@v-yiruan-msft  - 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 

[Malsk1_1]

@v-yiruan-msft 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]

@v-yiruan-msft any ideas why it stops working when you add a new month?

[PaulDBrown]

Sorry, does your table only contain the data you have provided?

If you have a date table, can you please share it? together with any other tables.
"the Actual spend is being populated" Where from?

"I have a Date filter using the Date table to filter out the Month End". What does this mean exactly?

Please provide an example as a table of what you are expecting to achieve. I can't figure out what the charts are calculating.

Thanks.

[Malsk1_1]

any help would be appreciated.. 

[PaulDBrown]

Please provide sample data (not an image) or a PBIX file (through OneDrive, Google Drive, Dropbox...) and a depiction of the model

[Malsk1_1]

@PaulDBrown  there is a PBIX provided by @v-yiruan-msft 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]

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]

@PaulDBrown thanks for this, i will test it out with more data and feedback.. once again, thank you!