Forum Discussion
Single Date Selection
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)
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.
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:
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."
