cancel
Showing results for
Did you mean:
Helper III

## Create Approximate Match Relationship

Hi There,

I have got two tables. Table A has  Date and Quantity Column as bellow:

 Date Qty 01-Jun-15 12246443 02-Jun-15 12737355 03-Jun-15 11901529 04-Jun-15 11574494 05-Jun-15 13583459 06-Jun-15 11980839 07-Jun-15 11727211

And Table B has bellow Rate Table:

 Date Rate 01-Jun-15 \$0.015 05-Jun-15 \$0.016 10-Jun-15 \$0.017

Now how do i join them so that i can calculate the revenue?

Thanks,

Sami

1 ACCEPTED SOLUTION
Memorable Member

There is no way to delete replies here!?  My answer was... not exactly on target 😞

You need a way to calculate the "most recent rate".  That is the last date in rate table, which is less than (or equal?) to the date in your quantities table.

Maybe something like... ?

=CALCULATE(
LASTNONBLANK(Rates[Date], MIN(Rates[Rate]),

FILTER(Rates, Rates[Date] <= MAX(Quantites[Date]))
)

4 REPLIES 4

Is your pricing a change schedule?  For instance the price is  \$0.015 for any orders placed between 01-Jun-15 and 04-Jun-15, at which the rate changes to \$0.016 for the next period?  We need a little more information to help you out properly.

Memorable Member

You will want to create a separate Calendar/Date table (that can have nice columns like Year-Month, etc).  You will create relationships from the Calendar[DateKey] to each of the data columns in your data/fact tables shown.

When building reports, you will use columns from the Calendar table, which will magically filter BOTH of the tables you have here.

Memorable Member

There is no way to delete replies here!?  My answer was... not exactly on target 😞

You need a way to calculate the "most recent rate".  That is the last date in rate table, which is less than (or equal?) to the date in your quantities table.

Maybe something like... ?

=CALCULATE(
LASTNONBLANK(Rates[Date], MIN(Rates[Rate]),

FILTER(Rates, Rates[Date] <= MAX(Quantites[Date]))
)

Helper III

Thanks,
Sami

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.