Reply
Frequent Visitor
Posts: 7
Registered: ‎06-12-2017
Accepted Solution

Display\Find The first Date that Inventory QTY goes Negative based on Running Net Inventory Totals

[ Edited ]

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


Accepted Solutions
Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

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

Attachment

All Replies
Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

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

Frequent Visitor
Posts: 7
Registered: ‎06-12-2017

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. 

Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

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

Frequent Visitor
Posts: 7
Registered: ‎06-12-2017

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

Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

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

Frequent Visitor
Posts: 7
Registered: ‎06-12-2017

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

Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

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

Frequent Visitor
Posts: 7
Registered: ‎06-12-2017

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

 

 

Super Contributor
Posts: 3,609
Registered: ‎09-27-2016

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

 

Attachment