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

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?

  • 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