cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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?

negativePNG.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

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])

1.PNG

Best Regards,
Angelia

 

View solution in original post

12 REPLIES 12
Highlighted
Microsoft
Microsoft

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

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.

2.PNG

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

Highlighted
Regular Visitor

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

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. 

Highlighted
Microsoft
Microsoft

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

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

Highlighted
Regular Visitor

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

 

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.

due_date.PNG

Highlighted
Microsoft
Microsoft

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

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

Highlighted
Regular Visitor

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

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])))

Highlighted
Microsoft
Microsoft

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

Hi @Grunstra,

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

Thanks,

Angelia

Highlighted
Regular Visitor

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

Here is pbix file. I only have sample data. I put in one item in this data. But you can still see when it goes negative. Thanks for the assistance. 

 

https://www.dropbox.com/sh/4cwfgboh9lhhura/AAD1-ypTPIhrNks67FScxMPPa?dl=0

 

 

Highlighted
Microsoft
Microsoft

Re: Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Tota

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])

1.PNG

Best Regards,
Angelia

 

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors