topic Projected inventory based on previous Physical inventory OR Projection in DAX Commands and Tips

Projected inventory based on previous Physical inventory OR Projection

SarahVenk — Wed, 19 Oct 2022 20:14:35 GMT

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!!

Re: Projected inventory based on previous Physical inventory OR Projection

Greg_Deckler — Wed, 19 Oct 2022 20:29:01 GMT

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

Re: Projected inventory based on previous Physical inventory OR Projection

SarahVenk — Wed, 19 Oct 2022 20:55:03 GMT

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