Single Date Selection

[OLoughanD]

I have many customer transactions:

Trans    Date/Time                    Customer             Currency

1           Jan 10 2019  11:35       MyCust                B

I also have a currency history table:

Start                                  Currency              Rate
Feb 13 2018   12:02             A                         0.9

Feb 14 2018   10:39             B                         1.1

Jun 25 2018    15:37            B                         1.12

Jun 25 2018    15:40            A                         0.87

Nov 17 2018   11:07            A                         0.88

Nov 17 2018   11:08            B                         1.13  

Jan 3 2019      10:30            A                         0.89

Jan 3 2019      10:31            B                         1.1

I want to find the rate that applies to each transaction.

• I can't use VLookUp because my transaction date/time is not a match for anything in my currency history table. Instead my date/time falls between the Start column entries, or in the case of the one I've shown after the last one.
• I can't use logical >= or <= because I don't have an End column in my currency history.

I figure I either need a function which can cope with this scenari, which means one that is smart enough to recognise that the currency of my transaction is B and the transaction occurs after Jan 3 2019 10:31  and so the rate should be 1.1

OR

I need a function which can calculate an End column in my currency history table based on the next Start entry for each currency.

[PattemManohar]

@OLoughanD Please try as below:

1. Create a "Rnk" field as below

Rnk = RANKX(FILTER(Test179Rnk,Test179Rnk[Currency]=EARLIER(Test179Rnk[Currency])),Test179Rnk[Start],,ASC)

2. Then, create a "End" field as below

End = 
VAR _End = LOOKUPVALUE(Test179Rnk[Start],Test179Rnk[Rnk],Test179Rnk[Rnk]+1,Test179Rnk[Currency],Test179Rnk[Currency])
RETURN IF(ISBLANK(_End),DATE(2099,12,31),_End-TIME(0,1,0))

In above logic, End date will be next occurance of that particular currency startdate minus 1 minute (It is because to avoid overlapping when you do lookup as EndDate of current row and StartDate of next row will be same). The latest currency will have high end date (or you can leave it blank as well)

[OLoughanD]

@PattemManohar

Your solution looks like an elegant one. I may have oversimplified the situation however. Please see the table I need to arrange:

And it needs to end up looking something like this:

[OLoughanD]

@PattemManohar

What I have realised I can go it create a GroupID field based on concatenating the CompanyID and the Currency. This would simplify my setup.

This gives me a single column identifying the company/currency combination for each record. I have tried to rank this using:

Rnk = RANKX(FILTER(CurrencyHistory,CurrencyHistory[GroupID]=EARLIER(CurrencyHistory[GroupID])),CurrencyHistory[chDateChanged],,ASC)

I get a message however saying "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

[OLoughanD]

Thanks for the comprehensive response. I'll try and let you know. I notice the calculated end date times are a few minutes after the start date times on the new row.