Forum Discussion

SarahVenk's avatar
SarahVenk
Regular Visitor
3 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 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!!



 

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 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())

     

  • SarahVenk's avatar
    SarahVenk
    Regular Visitor
    3 years ago

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