Forum Discussion
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 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.
11 Replies
any help would be appreciated..
Please provide sample data (not an image) or a PBIX file (through OneDrive, Google Drive, Dropbox...) and a depiction of the model
PaulDBrown there is a PBIX provided by Anonymous 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 see file located in the share -->
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..
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.
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
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!
