cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
samihuq
Helper III
Helper III

Create Approximate Match Relationship

Hi There,

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

 

DateQty
01-Jun-1512246443
02-Jun-1512737355
03-Jun-1511901529
04-Jun-1511574494
05-Jun-1513583459
06-Jun-1511980839
07-Jun-1511727211

 

And Table B has bellow Rate Table:

 

DateRate
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

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]))
)

View solution in original post

4 REPLIES 4
ALeef
Advocate III
Advocate III

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.

 

 

scottsen
Memorable Member
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.

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]))
)

View solution in original post

Sorry for the late reply but thanks, i had had it solved with kinda same solution.

 

Thanks,
Sami

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors