compare last week value and Current week for Inventory

[PeterM]

Hello,

I am trying to Display last week result and current week result Location wise to compare the Inventory.

For this, I have created two tables 1.LastweekData and 2.CurrentweekData table.

When I am trying to display I can see the only total result of two tables but not location wise also I tried to link tables with Itemid, locationID  but I see an error saying column must have a unique value. Please Help...

Note: Tables don't have Date key (My columns ITEMID,Location,Locationid,value$)

My result should be 

Locationid   LocationName    CurrentWeekValue   LastweekValue

                                                    From Table1         From Table2   

1                       CA                       24$                        23$

2                      TX                         23$                         28$

Please Let me know if I can do this any other way?

[v-ljerr-msft]

Hi @PeterM,

Have you tried the solution provided by @TomMartens above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here. 

Regards

[TomMartens · Accepted solution]

Hey,

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

Please be aware that you may overlook values that are not present in the CurrentweekData table, but have values in your LastweekData table.

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.

Hopefully this is what you are looking for

Regards

Tom