Create Relationship between Two Tables to represent inventory (HELP!)

[Anonymous]

In my dashboard, I am trying to represent the idea of sold inventory and avaialble inventory.

My inventory file is a flat file created representing all of my inventory for 2017 and 2018.

• Each market has 2 "slots" per month to sell.  An Early and Late slot available for purchase

My sales file includes all my sales within for 2017 and 2018 to date.

Each sample file can be found here

The goal is to create a relationship between the two files, and within all my inventory represent what is sold/available in each Market.

In my dashboard representation, I am trying to identify the following more less in a matrix view:

• Show all inventory as a rows for 2017/2018
• Each row will show whether an inventory slot is Sold or Available, if sold what the start date is, and whether it is an early or late slot (see below)

Market     | Target Date | Status     | Start Date  | Assignment

Iowa          | 1/10/2017     | Sold        | 1/11/2017    | Early                   

Delaware   | 1/10/2017     |Available |                      | Early

Currently, I am getting the following message, so I can't even begin to build out my dashboard, until I can figure out how to relate the tables.

Appreciate any help the community can provide.  Please offer link to pbix file if you are going to work with the sample data.

[v-sihou-msft]

@Anonymous

In this scenario, there's no columns with unique values in either table. It's not possible to directly build relationship between both tables.

And I still can't understand your logic. Based on your expected result, you aggregate Market field on first sale "slot" in 2017 Jan. But I can't figure out where "Start Date" and "Status" are from. Do you want to analyze your data on month level? Can you explain more about your expected result?

Regards

[Anonymous]

@v-sihou-msft

Thank you for your help.

I need to create the notion of total inventory and represent what portion of the inventory is either sold or available.  The inventory file represents the total amount of inventory I can sell in each market for 2017 and 2018.  The Sales Report represents the number of sales I have to date.  

I am trying to create a relationship between the two files that will illustrate a matrix view that represents a "complete view" of inventory with a value of Sold or Available for each row of Invnetory.  In essence, I am looking to replace a row of available inventory, when a slot is sold from the sales report.

With regard to your specific questions the Start Date is the day the campaign starts.

For example, if you look at Row 2 of the Sales Report:

Alpha

Back to School

Delaware

6/13/2017

6/13/2017

Early

this row would replace Row 60 of the inventory report (since this slot is sold)

Delaware

6/13/2017

Early

With regard to the "Early" or "Late" comment...

Anything that has a Start Date <= the 15th of the month is the "Early" slot for that month.  Anything with a "Start Date" of >15th is a "Late" slot for that month.

Each market only has one Early and one Late slot to sell in a given month.

The matrix view would look something like this in its final form:

Market	Advertiser	Status	Target Date	Start Date	Assignment
Delaware	Gamma	Sold	6/22/2017	6/22/2017	Late
Iowa		Available	6/22/2017		Late
Delaware	Alpha	Sold	6/13/2017	6/13/2017	Early
Iowa		Available	6/13/2017		Early

From the table above, you can see that Sold rows stem directly from the Sales Report.  The Available rows are from the Inventory report.  This table shows "June's" inventory for both Markets.

Please let me know if you have any additional questions, and thank you for your insight!