Forum Discussion

SarahVenk's avatar
SarahVenk
Regular Visitor
4 years ago

Projected inventory based on previous Physical inventory OR Projection

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 ...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

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())