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
PeterM
New Member

compare last week value and Current week for Inventory

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?

 

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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. Smiley Happy

 

Regards

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.