Forum Discussion
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 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
I made some minor modifications to suite my case but your method got me on the right track.
CostSavings_RateLookup =VAR TransactionDate = [Start Date]RETURNCALCULATE(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))
2 Replies
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] - _maxI made some minor modifications to suite my case but your method got me on the right track.
CostSavings_RateLookup =VAR TransactionDate = [Start Date]RETURNCALCULATE(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))
