Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PBI_Curve
Helper I
Helper I

Running Increase of Forecast based on Actuals

I'm a bit stumped with this one.

 

I have (1) Forecasts for the rest of this year and (2) Actual figures to this point.

I would like to have a third category based off the other two. This set of values would simply be the same as the (1) Forecasts for months in the future and (2) Actual figures for months in the past.

 

Therefore this (3) "Fluid Forecast" would be a forecasting that increases or reduces the overall total forecasted for a period based on the actual figures.

 

For example: For Q2 of this year, I've forecasted $100,000 p/month for April, May & June ($300,000 total). However my actual for April was $50,000 and for May was $100,000. As a result, the overall forecasted total for Q2 would drop by $50,000.

 

As June hasn't happened yet, it is still forecasted to take in $100,000 but because April fell short by $50,000, our overall total forecast for this period also reduces by $50,000.

 

Hopefully that is partially understandable. Any help would be appreciated.

 

1 ACCEPTED SOLUTION

@PBI_Curve

 

So here is your answer.

 

1 -  I've created an index column in the data tab > power query editor > index column - - I name it "MonthNumber" (Because I can't tell powerbi the month column is a data so it's annoying ...) 

 

So now that I have a column where Jan = 1 ; Feb = 2 etc....

 

I'm creating 

 

2 - Cumulative Forecast 

Cumulative Forecast = 
CALCULATE (
    SUM (YourTable[ Forecast]);
    ALL (YourTable);
    YourTable[MonthNumber] <= EARLIER (YourTable[MonthNumber])
)

3 - Cumulative Actual

Cumulative Actual = 
CALCULATE (
    SUM (YourTable[Actual]);
    ALL (YourTable);
    YourTable[MonthNumber] <= EARLIER (YourTable[MonthNumber])
)

 

4 - A column than I call 'before delta' and then the delta column (but perhaps you can do both in one, I'm not a pro)

 

BeforeDelta = IF(YourTable[Actual]=0;0;YourTable[ Forecast]-YourTable[Actual])

Delta = 
CALCULATE (
    SUM (YourTable[BeforeDelta]);
    ALL (YourTable);
    YourTable[MonthNumber] <= EARLIER (YourTable[MonthNumber])
)

And finally I'm creating 

 

5 - Cumulative Fluid Forecast

Cumulative Fluid Forecast = YourTable[Cumulative Forecast]-YourTable[Delta]

 

It looks like that in the end

 

 

lotsofcumulative.PNG

 

- Quentin

View solution in original post

11 REPLIES 11
quentin_vigne
Solution Sage
Solution Sage

Hi @PBI_Curve

 

In order to solve your problem we will a sample of dummy data and the expected result 

If you are not sure how to do that, please read this useful post : https://powerbi.microsoft.com/en-us/calculator/

 

 

- Quentin

alexei7
Continued Contributor
Continued Contributor

Hi PBI_Curve,

 

This should be possible in DAX.

Are you provide some sample data - or make some up so that we can provide a formula for you?

 

Thanks

Alex

Month Forecast Cumulative Forecast Actual(Cumulative) Fluid ForecastDelta
Jan $      50,000.00 $                  50,000.00 €   50,000.00 $                          50,000.00 $               -  
Feb $      50,000.00 $                100,000.00 €   50,000.00 $                       100,000.00 $               -  
Mar $      75,000.00 $                175,000.00 €   50,000.00 $                       150,000.00 $25,000.00
Apr $    100,000.00 $                275,000.00 €   50,000.00 $                       200,000.00 $75,000.00
May $    100,000.00 $                375,000.00 € 100,000.00 $                       300,000.00 $75,000.00
Jun $      75,000.00 $                450,000.00  $                       375,000.00 $75,000.00
Jul $      75,000.00 $                525,000.00  $                       450,000.00 $75,000.00
Aug $      75,000.00 $                600,000.00  $                       525,000.00 $75,000.00
Sep $    100,000.00 $                700,000.00  $                       625,000.00 $75,000.00
Oct $    100,000.00 $                800,000.00  $                       725,000.00 $75,000.00
Nov $    100,000.00 $                900,000.00  $                       825,000.00 $75,000.00
Dec $    120,000.00 $             1,020,000.00  $                       945,000.00 $75,000.00

 

Is this sufficient?

 

Cheers.

Hi @PBI_Curve

 

So if you want to have your Cumulative Fluid Forecast, for this situation you only need to make this : 

 

(Cumulative) Fluid Forecast = = YourTable[Cumulative Forecast]-YourTable[Delta]

 

It will work only if Delta is calculated automaticaly.

 

- Quentin

Hi @quentin_vigne,

 

Sorry, I'm explaining this fairly badly.

 

Basically, all I would have at hand is the below table.

I'm hoping to find a way to create the "Cumulative Fluid Forecast" column where every time a month ends, it replaces the Forecast figure with the actual figure from the, now completed, month.

 

Therefore the total for the "Fluid Forecast" would change every time a month ends and an actual figure is automatically entered into that column replacing the forecast.

 

Month Forecast Cumulative ForecastActualActual Cumulative
Jan $      50,000.00 $                  50,000.00 €   50,000.00 €   50,000.00
Feb $      50,000.00 $                100,000.00 €   50,000.00 € 100,000.00
Mar $      75,000.00 $                175,000.00 €   50,000.00 € 150,000.00
Apr $    100,000.00 $                275,000.00 €   50,000.00 € 200,000.00
May $    100,000.00 $                375,000.00 € 100,000.00 € 300,000.00
Jun $      75,000.00 $                450,000.00  
Jul $      75,000.00 $                525,000.00  
Aug $      75,000.00 $                600,000.00  
Sep $    100,000.00 $                700,000.00  
Oct $    100,000.00 $                800,000.00  
Nov $    100,000.00 $                900,000.00  
Dec $    120,000.00 $             1,020,000.00  

 

Cheers.

@PBI_Curve

 

Ok, we are going to make it simple so that I understand everything ! 

Can you post here what you have. (The table that you have only) 

 

After that tell me the columns you want me to calculate. (At the moment, I've understood that you need the Delta Column and the Cumulative Fluid forecast, that's all)

 

- Quentin

So this will be all I have. Forecasts that are provided to me at the start of the year and actuals that are added to/updated once a month ends.

 

Month Forecast Actual
Jan $      50,000.00 €   50,000.00
Feb $      50,000.00 €   50,000.00
Mar $      75,000.00 €   50,000.00
Apr $    100,000.00 €   50,000.00
May $    100,000.00 € 100,000.00
Jun $      75,000.00 
Jul $      75,000.00 
Aug $      75,000.00 
Sep $    100,000.00 
Oct $    100,000.00 
Nov $    100,000.00 
Dec $    120,000.00 

 

What I want from the "Fluid Forecast" is for it to simply copy the values across from the "Forecast" column for each month unless that month has ended, in which case the corresponding monthly figure from the "Actual" column will replace the current value (which is the corresponding Month's "Forecast" value).

 

So I just need the one column, "Fluid Forecast" from this.

 

From the above table, the "Fluid Forecast" for June 2018 would be $75,000 (the exact same figure from the "Forecasted" column). When June ends, I want that same cell to change from $75,000 to whatever the "Actual" figure was for June.

 

Thanks.

@PBI_Curve

 

So here is your answer.

 

1 -  I've created an index column in the data tab > power query editor > index column - - I name it "MonthNumber" (Because I can't tell powerbi the month column is a data so it's annoying ...) 

 

So now that I have a column where Jan = 1 ; Feb = 2 etc....

 

I'm creating 

 

2 - Cumulative Forecast 

Cumulative Forecast = 
CALCULATE (
    SUM (YourTable[ Forecast]);
    ALL (YourTable);
    YourTable[MonthNumber] <= EARLIER (YourTable[MonthNumber])
)

3 - Cumulative Actual

Cumulative Actual = 
CALCULATE (
    SUM (YourTable[Actual]);
    ALL (YourTable);
    YourTable[MonthNumber] <= EARLIER (YourTable[MonthNumber])
)

 

4 - A column than I call 'before delta' and then the delta column (but perhaps you can do both in one, I'm not a pro)

 

BeforeDelta = IF(YourTable[Actual]=0;0;YourTable[ Forecast]-YourTable[Actual])

Delta = 
CALCULATE (
    SUM (YourTable[BeforeDelta]);
    ALL (YourTable);
    YourTable[MonthNumber] <= EARLIER (YourTable[MonthNumber])
)

And finally I'm creating 

 

5 - Cumulative Fluid Forecast

Cumulative Fluid Forecast = YourTable[Cumulative Forecast]-YourTable[Delta]

 

It looks like that in the end

 

 

lotsofcumulative.PNG

 

- Quentin

Worked a treat.

Thanks a million!

@PBI_Curve

 

You're welcome, have a good day

 

- Quentin

Could you just keep it simple and use an IF formulae i.e if(Actual=blank(),Forecast,Actual) ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.