Forum Discussion

samihuq's avatar
samihuq
Helper III
10 years ago
Solved

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-J...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    10 years ago

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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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

  • samihuq's avatar
    samihuq
    Helper III
    10 years ago

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

     

    Thanks,
    Sami