compare last week value and Current week for Inventory

Question

Hello,&nbsp;I am trying to Display last week result and current week result Location wise to&nbsp;compare the&nbsp;Inventory.For this, I have created two tables 1.LastweekData and 2.CurrentweekData table.&nbsp;When I am trying to display I can see the only total result of two tables but not location wise also I tried to&nbsp;link&nbsp;tables with Itemid,&nbsp;locationID&nbsp; but I see an error saying column&nbsp;must have a unique value. Please Help...&nbsp;&nbsp;Note: Tables don't&nbsp;have Date key (My columns ITEMID,Location,Locationid,value$)My result should be&nbsp;&nbsp;Locationid&nbsp; &nbsp;LocationName&nbsp; &nbsp; CurrentWeekValue&nbsp; &nbsp;LastweekValue&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; From Table1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;From Table2&nbsp; &nbsp;&nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;24$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 23$2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TX&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;23$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;28$Please Let me know if I can do this any other way?&nbsp;&nbsp;

tommartens · Accepted Answer

Hey,&nbsp;I would create a calculated column in your currentweek table like so:LastWeekValue = 
LOOKUPVALUE(
  'LastweekData'[Value]
  ,'LastweekData'[Locationid], 'CurrentweekData'[Locationid]  ,'LastweekData'[ITEMID], 'CurrentweekData'[ITEMID]
)I assume that the column ITEMID is not something like a product id but uniquely identifies a row in each table, meaning it's a technical column and not a business related column. In this case you have to delete the line that contains the reference to ITEMID in the above statement.Here is a more information about the LOOKUPVALUE function:https://msdn.microsoft.com/de-de/library/gg492170(v=sql.120).aspx&nbsp;Please be aware that you may overlook values that are not present in the CurrentweekData table, but have&nbsp;values in your LastweekData table.&nbsp;You may consider a little remodelling of your data model, by creating a Location Table containing the columns LocationID and LocationName with the unique values of both tables and then create a relationship with the Currentweek and Lastweek table and then just use the Location information from this table in your reports.&nbsp;Hopefully this is what you are looking for&nbsp;RegardsTom

Forum Discussion

compare last week value and Current week for Inventory

2 Replies

Related content

Inventory and Stock balance calculation

Inventory Forecast

Inventory Management Dashboard

Inventory opening closing

Inventory Management Dashboard