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_Date of 6/16/2017 when I will have Negative -546 Inventory. I need a way to pick out that date of 6/16/2017 based on that first negative inventory so I can display that in my other table.  Can anyone help me figure this out?

Need Help
Tips and Tricks
Tutorial Requests

12 Replies

  • 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