Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Grunstra
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

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
v-huizhn-msft
Employee
Employee

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

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. 

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

 

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

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

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

Hi @Grunstra,

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

Thanks,

Angelia

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

 

 

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

 

Sweet!! Thanks.  How can I also get the Order QTY related to the due when it first went Negative?

Hi @Grunstra,

I use the min function, it has calculated the earliest date went Negative. 

Best Regards,
Angelia

Any way to get the ON Hand Inventory QTY on that Due Date?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.