Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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..
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
@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
@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!
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.
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
@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
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
Proud to be a Super User!
Paul on Linkedin.
@PaulDBrown thanks for this, i will test it out with more data and feedback.. once again, thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |