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,

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

    • 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
        Icon for Microsoft Employee rankMicrosoft 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