I'd start with something like this which is the most common way to get a previous value:

WITH ctePFE

AS (

SELECT

t.portfolioid,

t.limittimeband,

MAX(t.maxexposure) AS RiskValue,

t.batchid,

ROW_NUMBER() OVER (PARTITION BY t.portfolioid, t.limittimeband, t.batchid ORDER BY t.timeband) AS rowNo

FROM

dw.factlimitutilizationbyportfolio t (NOLOCK)

INNER JOIN dw.riskmeasuredefinition rmd (NOLOCK)

ON t.riskmeasuredefinitionid = rmd.riskmeasureid AND

rmd.riskmeasurename = 'PFE_LC' AND

t.isfact = 1

GROUP BY

t.portfolioid,

t.limittimeband,

t.batchid

),

ctePFEwithPreviousValue

AS (

SELECT

PFE1.portfolioid,

PFE1.limittimeband,

PFE1.RiskValue,

PFE1.batchid,

PFE2.RiskValue AS previousRiskValue

FROM

ctePFE AS pFE1

LEFT JOIN ctePFE AS PFE2

ON PFE1.portfolioid = PFE2.portfolioid AND

pFE1.rowNo = PFE2.rowNo + 1

)

SELECT --bi.cobdate,

fact.batchid,

fact.mastergroup,

fact.counterparty,

fact.counterpartyname,

fact.parentcounterpartyname AS ParentCounterparty --If No Parent, then show the original CP as Parent

,

fact.portfoliolevelcd AS AggregationScheme,

fact.portfolionodevalue AggregationNode,

ptycc.anzccr AS CCR,

ptycc.securityindicator AS SI,

fact.limittimeband,

fact.limitstartdt AS [Start_Date],

fact.limitenddt AS [End_Date],

COALESCE(ext.currency, fact.limitcurrency) AS LocalCurrency,

fact.limitcurrency AS LimitCCY,

COALESCE(fun.currency, fun1.limitcurrency) LocalExchnagerate,

fun1.limitcurrency AS LimitExchnagerate,

(fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount) AS Limit

--,fun.CurvePointValue LocalCurrency

,

fun1.curvepointvalue Limitcurrency,

pfe.riskvalue AS Exposure

--,pfe.RiskValue2 as "ExposureT-1"

,

((fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount) - pfe.riskvalue) AS Availability,

(CASE WHEN ((fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount)) IS NULL OR

pfe.riskvalue IS NULL THEN 0

WHEN ((fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount)) = 0 AND

pfe.riskvalue > 0 THEN 1

WHEN ((fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount)) = 0 AND

pfe.riskvalue = 0 THEN 0

ELSE CAST((ISNULL(pfe.riskvalue, 0) / ((fun1.curvepointvalue / fun.curvepointvalue) *

CONVERT(FLOAT, fact.limitamount))) AS FLOAT)

END) AS Utilisation,

ptycc.creditcontrolpoint AS ControlPoint,

fact.[CollateralAgreementCd] CollateralApplied,

fact.[NettingAgreementCd] NettingApplied,

fact.israzor

FROM

dw.[factlimitutilizationbyportfolio] FACT (NOLOCK)

INNER JOIN (

SELECT

br.batchrunid BatchID,

bi.businessdate CobDate,

bi.batchinstanceid,

br.startdatetime AS ReportingDate

FROM

logging.batchrun br (NOLOCK)

INNER JOIN logging.batchinstance bi (NOLOCK)

ON br.batchinstanceid = bi.batchinstanceid

) BI

ON fact.batchid = Bi.batchid

INNER JOIN dw.partycreditcontrol ptycc (NOLOCK)

ON fact.counterpartyid = ptycc.partyid AND

(

ptycc.effstartdate <= bi.cobdate AND

ptycc.effenddate > bi.cobdate

)

INNER JOIN dw.portfolio port (NOLOCK)

ON fact.portfolioid = port.portfolioid AND

port.providersystemcd = 'Razor' AND

port.portfoliolevelcd = 'Customer Asset Group' AND

port.effstartdate <= bi.cobdate AND

port.effenddate > bi.cobdate

LEFT JOIN dw.portfoliobridge bport (NOLOCK)

ON (

bport.tgtportfolioid = fact.portfolioid AND

bport.tgtprovidercd = 'Razor' AND

bport.effstartdate <= bi.cobdate AND

bport.effenddate > bi.cobdate

)

LEFT JOIN ctePFEwithPreviousValue pfe

ON bport.srcportfolioid = pfe.portfolioid AND

bport.srcprovidercd = 'CRE' AND

fact.limittimeband = pfe.limittimeband AND

fact.batchid = PFE.batchid

LEFT JOIN (

SELECT DISTINCT

portfolioid,

currency,

runid

FROM

extract.razorportfoliotraderelation

) ext

ON fact.batchid = ext.runid AND

PFE.portfolioid = ext.portfolioid

LEFT JOIN (

SELECT

cpt.curvepointvalue,

cdt.observationdt,

cid.curveidentifier currency

FROM

dw.curveidentifier cid (NOLOCK)

INNER JOIN dw.curvedata cdt (NOLOCK)

ON cid.curveid = cdt.curveid

INNER JOIN dw.curvepoint cpt (NOLOCK)

ON cdt.curvedataid = cpt.curvedataid

WHERE

cid.curvetype = 'Exchange' AND

cid.curvedomain = 'QuIC' AND

cid.islatest = 1 AND

cdt.islatest = 1 AND

cpt.islatest = 1 AND

cdt.ccycd = 'USD'

) fun

ON fun.observationdt = bi.cobdate AND

fun.currency = ext.currency

LEFT JOIN (

SELECT

cpt.curvepointvalue,

cdt.observationdt,

cid.curveidentifier LimitCurrency

FROM

dw.curveidentifier cid (NOLOCK)

INNER JOIN dw.curvedata cdt (NOLOCK)

ON cid.curveid = cdt.curveid

INNER JOIN dw.curvepoint cpt (NOLOCK)

ON cdt.curvedataid = cpt.curvedataid

WHERE

cid.curvetype = 'Exchange' AND

cid.curvedomain = 'QuIC' AND

cid.islatest = 1 AND

cdt.islatest = 1 AND

cpt.islatest = 1 AND

cdt.ccycd = 'USD'

) fun1

ON fun1.observationdt = bi.cobdate AND

fun1.limitcurrency = fact.limitcurrency

WHERE

isfact = 0

This solution probably won't work out of the box because I don't know the database to know what all the columns mean, but the key point is the CTE with row_number. If you get ROW_NUMBER configured correctly the JOIN on rowNO = rowNO + 1 get's you the previous value if you go with the default Ascending sort order.