Projected inventory based on previous Physical inventory OR Projection

[SarahVenk]

Hi!! I'm going crazy trying to calculate the Projected column in Power BI as per the formula below -

All I need is - for a combination of warehouse and item,
if the value Phy in the previous month is <>0, Projected = Previous month Phy+previous month Backlog
If the value Phy in the previous month is= 0, Projected = Previous month's Projected+previous month Backlog

Appreciate your help!!

[SarahVenk]

Thanks for your response! Is there any way to achieve this in Power Query? Any workarounds?

[Greg_Deckler]

@SarahVenk Well, the first part is below but your second part delves into recursion and thus misery and can't be done in DAX:

Project Column = 
  VAR __Item = [Item]
  VAR __WH = [Warehouse]
  VAR __LMEnd = EOMONTH([Date],-1)
  VAR __LM = DATE(YEAR(__LMEnd),MONTH(__LMEnd),1)
  VAR __Table = FILTER('Table',[Warehouse] = __WH && [Item] = __Item && [Date] = __LM)
  VAR __LMPhy = MAXX(FILTER(__Table,[Phy])
  VAR __LMBacklog = MAXX(FILTER(__Table,[Backlog])
RETURN
  IF(__LMPhy <> 0,__LMPhy + __LMBacklog, BLANK())