Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
- Quentin
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
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 Forecast | Delta |
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 Forecast | Actual | Actual 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.
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.
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
- Quentin
Worked a treat.
Thanks a million!
Could you just keep it simple and use an IF formulae i.e if(Actual=blank(),Forecast,Actual) ?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |