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
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Grunstra,

First, right click your table->Query Edit, you will get Query Edit Navigator, add a index column by click "Index Column" under "Add column" on Home page.



Second, you can create a new table by clicking "New Table" under Modeling on Home page. Type the following formula.

Table 3 = SELECTCOLUMNS(FILTER(Table,Table[Value]=MINX(FILTER(Table,Table[NetInvRT]<0),Table[index])),"Date",Table[due_date])


Best Regards,
Angelia

Grunstra's avatar
Grunstra
Regular Visitor
9 years ago

Thank you for your reply. I did follow your suggestion but I'm not clear on how I would then associate that new table column  into my table visual? When I grad that Date column from the newly create Table 3 into my Table visual it makes all the other data disappear and nothing appears. 

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

    Hi Grunstra,

    The solution above create a new table including the date of 6/16/2017 based on that first negative inventory. How do you plan to add it to your visual, what visual you use? Could you please share a screenshot? You can use the new table's data to create visual.

    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