Forum Discussion

Kreichman's avatar
Kreichman
Regular Visitor
2 years ago
Solved

Matching A Single Date Across Multiple Dates In A Dataset

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 ...
Need Help
  • Kreichman's avatar
    Kreichman
    2 years ago

    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
            )
        )
amitchandak's avatar
amitchandak
Super User
2 years ago

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's avatar
Kreichman
Regular Visitor
2 years ago

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