topic Re: Calculating a Forecast looking at Previous Values in DAX Commands and Tips

Calculating a Forecast looking at Previous Values

nawillia711 — Thu, 09 Jul 2020 18:32:56 GMT

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

Re: Calculating a Forecast looking at Previous Values

Greg_Deckler — Thu, 09 Jul 2020 19:02:49 GMT

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

Re: Calculating a Forecast looking at Previous Values

nawillia711 — Fri, 10 Jul 2020 14:06:26 GMT

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?

Re: Calculating a Forecast looking at Previous Values

Anonymous — Fri, 10 Jul 2020 15:59:32 GMT

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!