Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 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.
@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)
Proud to be a PBI Community Champion
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."
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |