Conditional Column calculation

SSAS and SSRS
1

Hello All,

I have the following SQL Query, what I need is to have a column let's Say Shortages. since I arrange or Sort the Query by Item, so If Item No_ = Previous Item No i.e Item No_ of the previous record, the Shortages will Sill shortages - [remaining Qty_] else Shortages will be QOH- Remaining Qty
> Select

	POC.[Prod_ Order No_] As [WO No_],
	POL.[Item No_] as [Prod. Item No_],
	POL.[Description] as [FG Decsription],
	POL.[Unit of Measure Code] as [Prod. Order Item UOM],
	POL.[Remaining Quantity] as [Prod. Order Quantity],
	POL.[Due Date] as [Due Date(Prod. Order)],
	POC.[Item No_] [Component No],
	POC.[Description] as [Component Desc],
	POC.[Manufacturer Code],
	POC.[Mfg_ Part No_],
	POC.[Due Date],
	Item.[Buyer],
	Item.[Customer No_],
	case 
		When Item.[Replenishment System] = 0 then 'Purchase'
		Else 'Prod. Order' 
	end as [Replenishment System],
	(Select ISNULL(Sum(Quantity),0) from [dbo].[XXXXXXX$Item Ledger Entry] where [Item No_]= POC.[Item No_]) As QOH,
	(Select ISNULL(Sum(Quantity),0) from [dbo].[XXXXXXX$Item Ledger Entry] where [Item No_]= POC.[Item No_] AND [Location Code] Like '%MRB%') As MRB_QTY,
	POC.[Remaining Quantity],
	(Select ISNULL(SUM([Outstanding Qty_ (Base)]),0) from [dbo].[XXXXXXX$Purchase Line] where [No_] = POC.[Item No_] AND [Document Type] = 1 AND [Type] = 2) as [Qty_on PO]

from dbo.[XXXXXXX$Prod_ Order Component] as POC
Inner Join dbo.[XXXXXXX$Prod_ Order Line] POL ON
	(POC.[Status] = POL.[Status] ) AND
	(POC.[Prod_ Order No_] = POL.[Prod_ Order No_]) AND 
	(POC.[Prod_ Order Line No_] = POL.[Line No_])
Inner Join dbo.[XXXXXXX$Item] as Item on
POC.[Item No_] = Item.[No_]
Where POC.[Status] in (1,2,3)
Order By POC.[Item No_],POC.[Variant Code],POC.[Location Code],POC.[Due Date],POC.[Status]

I attached an excel table also for this calculation, the formula used is

=IF(A2=A1,E1-D2,C2-D2)
ExcelFormula

2

If you provide DDL and sample data we can help. We need easily consumable sample to help. I would start with the lag function, but also need to know what version of SQL you are using