Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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])
Best Regards,
Angelia
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
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.
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])
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?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |