Using Previous Value in column Calculate that references other columns

[Jz77777]

Hi Everyone,   I been tasked with taking an excel model that somehow doesn't have circular dependancies and automate it with Power BI.  The calculated column I am trying to create uses the previous value as a baseline to calculate the next value.   The bonus is that both the previous value and new value are used in the calculation of the next value.   The excell template has only one object (wind turbine) and the power BI I have has about a 100.   After Indexed by turbine and date, I am still stuck with how to use the previous value.  The part I am stuck on is the VAR Previous F Model Column.  F stands for exactly what you think.  Any suggestions? 

F Model Column = 
VAR CurrentTurbine = 'MainBearing Analytic'[Turbine]
VAR CurrentIndexCC = 'MainBearing Analytic'[Index CC]
VAR PreviousIndexCC = CurrentIndexCC - 1
VAR StartingValue = 'MainBearing Analytic'[MB Temp]
-- Retrieve Previous Row Values
VAR PreviousGrade = 
    CALCULATE(
        MAX('MainBearing Analytic'[Overall Grade]),
        FILTER(
            'MainBearing Analytic',
            'MainBearing Analytic'[Turbine] = CurrentTurbine &&
            'MainBearing Analytic'[Index CC] = PreviousIndexCC
        )
    )

VAR PreviousFModel = 
    CALCULATE(
        MAX('MainBearing Analytic'[F Model Column]),
        FILTER(
            'MainBearing Analytic',
            'MainBearing Analytic'[Turbine] = CurrentTurbine &&
            'MainBearing Analytic'[Index CC] = PreviousIndexCC
        )
    )

VAR CurrentmbTemp =
    CALCULATE(
        MAX('MainBearing Analytic'[MB Temp]),
        FILTER(
            'MainBearing Analytic',
            'MainBearing Analytic'[Turbine] = CurrentTurbine &&
            'MainBearing Analytic'[Index CC] = CurrentIndexCC
        )
    )

VAR CurrentambTemp =
    CALCULATE(
        MAX('MainBearing Analytic'[Ambient Temp]),
        FILTER(
            'MainBearing Analytic',
            'MainBearing Analytic'[Turbine] = CurrentTurbine &&
            'MainBearing Analytic'[Index CC] = CurrentIndexCC
        )
    )
VAR Vis =
    CALCULATE(
        IF(
            ABS(PreviousFModel)-ABS(CurrentmbTemp)<5,
        10^(10^((LOG(CurrentmbTemp+273.15)*LOOKUPVALUE(Units[MB-Grease slope],Units[Name],CurrentTurbine)
        +LOOKUPVALUE(Units[MB-Grease Y-int],Units[Name],CurrentTurbine)))),
        10^(10^((LOG(PreviousFModel+273.15)*LOOKUPVALUE(Units[MB-Grease slope],Units[Name],CurrentTurbine)
        +LOOKUPVALUE(Units[MB-Grease Y-int],Units[Name],CurrentTurbine))))),
        FILTER(
            'MainBearing Analytic',
            'MainBearing Analytic'[Turbine] = CurrentTurbine &&
            'MainBearing Analytic'[Index CC] = CurrentIndexCC
        )
    )
VAR PreRPM =
    CALCULATE(
        MAX('MainBearing Analytic'[RPM]),
        FILTER(
            'MainBearing Analytic',
            'MainBearing Analytic'[Turbine] = CurrentTurbine &&
            'MainBearing Analytic'[Index CC] = PreviousIndexCC
        )
    )

VAR PreviousFHeatGen = 
    CALCULATE(
        If(
            PreviousGrade="Good",
            LOOKUPVALUE(Units[MB-Heat Gen Coefficent ],Units[Name],CurrentTurbine)*
            PreRPM*
            Vis,
            BLANK()
            ),
        FILTER(
            'MainBearing Analytic',
            'MainBearing Analytic'[Turbine] = CurrentTurbine &&
            'MainBearing Analytic'[Index CC] = PreviousIndexCC
        )
    )
    
VAR PreviousFHeatXfer = 
    CALCULATE(
        IF(
    PreviousGrade = "Good",
    LOOKUPVALUE(
        Units[MB-Heat Xfer Coefficent ],
        Units[Name], CurrentTurbine
    ) *
    (
        CurrentmbTemp -
        CurrentambTemp +
        LOOKUPVALUE(
            Units[MB-Offset],
            Units[Name], CurrentTurbine
        )
    ),
    BLANK()
)
,
        FILTER(
            'MainBearing Analytic',
            'MainBearing Analytic'[Turbine] = CurrentTurbine &&
            'MainBearing Analytic'[Index CC] = PreviousIndexCC
        )
    )

-- Return the F Model value based on conditions
RETURN
IF(
    CurrentIndexCC = 0,
    'MainBearing Analytic'[MB Temp],
    IF(
        PreviousGrade = "Bad",
        'MainBearing Analytic'[MB Temp],
        PreviousFModel + PreviousFHeatGen + PreviousFHeatXfer
    )
)

[amitchandak · Accepted solution]

@Jz77777 , we can not do the recursive calculations in power bi.

You need thing if cumulative can do that

example

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

example with date table

Onhand BOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))


onhand EOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=Max(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <= Max(date[date])))