Forum Discussion
Calculating a Forecast looking at Previous Values
Hello,
What I am trying to do is calculate a forecast using the previous row's number. So for the first row of the column "forecast" the formula is summing all the numbers in column "S." Then for every other row it uses the previous "Ending" value. Just need to know if/how this would be possible in Power BI.
| Date | S | U | G | Forecast | Ending (U-G+Forecast) |
| 1/01/2020 | 0.08 | 0.24 | 0.68 | 4.86 | -3.94 |
| 31/12/2019 | 0.53 | 0.34 | 0.96 | -3.94 | 5.24 |
| 30/12/2019 | 0.24 | 0.39 | 0.32 | 5.24 | -4.53 |
| 29/12/2019 | 0.60 | 0.88 | 0.58 | -4.53 | 5.99 |
| 28/12/2019 | 0.84 | 0.52 | 0.13 | 5.99 | -5.34 |
| 27/12/2019 | 0.60 | 0.29 | 0.01 | -5.34 | 5.64 |
| 26/12/2019 | 0.71 | 0.62 | 0.12 | 5.64 | -4.90 |
| 25/12/2019 | 0.71 | 0.30 | 0.59 | -4.90 | 5.78 |
| 24/12/2019 | 0.55 | 0.62 | 0.03 | 5.78 | -5.13 |
3 Replies
nawillia711 - Should be possible, but tough to understand your description. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Basically what I would like to do is if row 1 of the column "ending" is showing the value 2, then row 2 of the column "forecast" should return 2 as its number. The if row 2 of "ending" shows 1.5 then row 3 of "forecast" would show 1.5 etc. Does that make more sense?
Hi nawillia711 ,
Create a Calculated Column
Ending = VAR filterdate = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Date] < EARLIER ( 'Table'[Date] ) ) ) RETURN CALCULATE ( MAX ( 'Table'[Forecast] ), FILTER ( 'Table', 'Table'[Date] = filterdate ) )Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)Did I answer your question? Mark my post as a solution!
