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

     

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft 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])))

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi Grunstra,

    Do you mind share your sample table in table format rather than picture? So I can reproduce it.

    Thanks,

    Angelia