Forum Discussion
Single Date Selection
I have many customer transactions: Trans Date/Time Customer Currency 1 Jan 10 2019 11:35 MyCust B I also have a currency his...
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.