Forum Discussion

samihuq's avatar
samihuq
Helper III
7 years ago
Solved

Approximate Match for Date

Hi, I have got two tables.   Table A:   Date Qty Rate 1-Jan-18 89 10 2-Jan-18 81 10 3-Jan-18 30 10 4-Jan-18 45 10 5-Jan-18 50 10 6-Jan-18 20 10 7-Jan-18 84 10 8-Jan-18 26 20 9-Jan-18 54...
Need Help
Show and Tell
Tutorial Requests
  • samihuq's avatar
    samihuq
    7 years ago

    Hi,

    Actually Found the solution by ourselves>

     

    Had to do two columns to achieve this, first find the approximate date [kind of vlookup true] and then find the corrosponding rate [kindof vlookup false]

     

    Date 2 = CALCULATE(LASTNONBLANK(Rate[Date].[Date],1),FILTER(Rate,Rate[Date].[Date]<=Data[Date].[Date]))

     

    and 

    Rate = CALCULATE(SUM(Rate[Rate]),FILTER(Rate,Rate[Date].[Date]=Data[Date 2].[Date]))

     

    Thanks,

    Sami

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi samihuq,

 

Do you want to compare the rate on the same day in TableA and TableB to check if they are matched? If so, you could use LOOKUPVALUE function to refer to 'TableA'[Rate] column in TableB.

 

New Column =
IF (
    TableB[Rate] = LOOKUPVALUE ( TableA[Rate], TableA[Date], TableB[Date] ),
    "Matched",
    "Not Matched"
)


Best regards,

Yuliana Gu

samihuq's avatar
samihuq
Helper III
7 years ago

Hi,

Actually Found the solution by ourselves>

 

Had to do two columns to achieve this, first find the approximate date [kind of vlookup true] and then find the corrosponding rate [kindof vlookup false]

 

Date 2 = CALCULATE(LASTNONBLANK(Rate[Date].[Date],1),FILTER(Rate,Rate[Date].[Date]<=Data[Date].[Date]))

 

and 

Rate = CALCULATE(SUM(Rate[Rate]),FILTER(Rate,Rate[Date].[Date]=Data[Date 2].[Date]))

 

Thanks,

Sami