Matching A Single Date Across Multiple Dates In A Dataset

[Kreichman]

I'm working on a project where I have a transaction table where the date of something occurs, call it a sale. I'm working to compare that value for a given date with a rates table. I have an example of what I'm trying to do below. I'm not sure if I need to be pulling this as a measure, or adding a calculated column to my report. If I don't have enough info to assist in answering the question please let me know. 

Thank you very much in advance for any assistance.


Example Rate table has an effective date of 1/1/2023 that rate will be say $100. New rate will be effective 4/1/2023 and it will be $150

The transaction table will have dates and rates of those listed below.

1/1/2023 $125

1/12/2023 $75

3/4/2023 $150

4/9/2023 $150

4/28/2023 $200

5/7/2023 $225

The difference for the dates from 1/1/2023 to 3/31/2023 would be $50 The difference for the dates after 4/1/2023 would be $125

[amitchandak]

@Kreichman , Try a new column like

Rate Difference =
VAR _TD = CALCULATE(
MAX('Rate'[Rate]),
'Rate'[Effective Date] <= _TD
)
VAR _max=
CALCULATE(
MAX('Rate'[Rate]),
'Rate'[Effective Date] = _TD
)
RETURN
'Transaction'[Rate] - _max

[Kreichman · Accepted solution]

I made some minor modifications to suite my case but your method got me on the right track.

CostSavings_RateLookup =

VAR TransactionDate = [Start Date]

RETURN

    CALCULATE(

        MAX('ABC-Cost_Savings_Checkbook'[Cost_Proposed]),

        FILTER('ABC-Cost_Savings_Checkbook',

            'ABC-Cost_Savings_Checkbook'[Effective_Date] <= TransactionDate &&

            'ABC-Cost_Savings_Checkbook'[Term_Date] >= TransactionDate

        )

    )