Forum Discussion

Grunstra's avatar
Grunstra
Regular Visitor
9 years ago
Solved

Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Totals

I need a way to find when an Item running Inventory On Hand QTY first goes negative and display its Due_date.. Example see screen shot where I have Running Inventory totals per Orders. I have a Due_D...
Need Help
Tips and Tricks
Tutorial Requests
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Grunstra,

    I thought your resource table in same table, I understand now. Please create a measure using the formula, then add it to your table report.

    New Due-Date = MINX(FILTER('OrdersOpen-All','OrdersOpen-All'[Net Inv RT]<0),'OrdersOpen-All'[due_date])



    Best Regards,
    Angelia

     

Grunstra's avatar
Grunstra
Regular Visitor
9 years ago

 

Here is a screen shot. Notice that Due date that is needed from the bottom Table visual, I need that Due date which is the first date that inventory is negative to be display at the top table visual in the screen shot. Associated by the Item number.

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Grunstra,

Please create measure using the formula and check if it works fine.

Due-Date = CALCULATE(MAX(Table[due_date]),FILTER(ALLEXCEPT(Table,Table[item]),Table[Value]=MINX(FILTER(Table,Table[NetInvRT]<0),Table[index])))


Best Regard,
Angelia

  • Grunstra's avatar
    Grunstra
    Regular Visitor
    9 years ago

    What would I put for your measure example of "Table[Value]=" section? 

     

    This is what I came up with but doesn't display any reuslts when I add it into the top table visual. Not sure if I have the correct fields.

     

    Due-Date = CALCULATE(MAX('OrdersOpen-All'[due_date]),FILTER(ALLEXCEPT('OrdersOpen-All','OrdersOpen-All'[item]),'OrdersOpen-All'[due_date]=MINX(FILTER('OrdersOpen-All','OrdersOpen-All'[Net Inv RT]<0),'OrdersOpen-All'[Index])))