Calculating a Forecast looking at Previous Values

[nawillia711]

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

[harshnathani]

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!

[Greg_Deckler]

@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/3395...

[nawillia711]

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?