Help! Create a relationship when a natural one does not exist

[Anonymous]

I have a table of the cost of items where there is a starting SKU and an ending SKU.

Imagine it looks like this:

Starting     Ending  Cost

1000           2000      1.00
2001           3000      2.00

3001           4000      3.00

I have another table of sales, it looks like this:

Date                        SKU Number     Units
12/30/2019        1001                        5
12/30/2019         1002                       6
12/31/2019        3010                       10

I need to understand how to write the code to determine the cost.  If the SKU is 3010, I would expect to get 10*3 = $30.00

[VasTg · Accepted solution]

@Anonymous 

Create a new calculated column in Table2 and use the DAX.

Column =
CALCULATE (
    SUM ( 'Table (2)'[Units] ) * SUM ( 'Table'[Cost] ),
    FILTER (
        'Table',
        'Table (2)'[SKU Number] >= 'Table'[Starting]
            && 'Table (2)'[SKU Number] <= 'Table'[Ending]
    )
)

If this helps, mark it as a solution

Kudos are nice too.

[Anonymous]

Thanks, I will check it out and come back shortly

[KHorseman]

Does the SKU number from table 2 not appear in any column on table 1? I'm not sure I understand what the Starting and Ending thing is. Is that a range of all the SKUs between 3001 and 4000? If that's the case, I would add a column in the query editor that creates a list of numbers in the range, then expand that to new rows, making a table that has a row for each SKU individually. I'll be back in a few minutes with some sample code...

[KHorseman]

In the query editor, add a custom column named SKU.

= List.Numbers([Starting], [Ending] - [Starting])

Next, at the top of the new column, you will see a little double arrow icon. Click on that and select "Expand to New Rows" and that will create a row for each SKU. Now load the table and you can create a relationship between the two tables.