Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I seek your help once again.
I have two tables as follows:
#1
Name | Amount in USD | Local Curency | Date | Relationship column |
Tom | 5000 | GBP | 25/03/2024 | GBP-25/03/2024 |
Peter | 57 | GBP | 10/02/2024 | GBP-10/02/2024 |
Sam | 457 | GBP | 16/01/2024 | GBP-16/01/2024 |
Tom | 16 | GBP | 02/02/2024 | GBP-28/02/2024 |
Peter | 150 | GBP | 03/02/2024 | GBP-03/02/2024 |
Sam | 877 | GBP | 02/04/2024 | GBP-02/04/2024 |
#2
Date | Currency | Xchrate | Relationship column |
01/02/2024 | AUD | 0.58 | AUD-01/02/2024 |
01/02/2024 | GBP | 1.02 | GBP-01/02/2024 |
02/02/2024 | AUD | 0.69 | AUD-02/02/2024 |
02/02/2024 | GBP | 1.05 | GBP-02/02/2024 |
03/02/2024 | AUD | 0.63 | AUD-03/02/2024 |
03/02/2024 | GBP | 1.002 | GBP-03/02/2024 |
04/02/2024 | AUD | AUD-04/02/2024 | |
04/02/2024 | GBP | GBP-04/02/2024 | |
05/02/2024 | AUD | AUD-05/02/2024 | |
05/02/2024 | GBP | GBP-05/02/2024 |
A relationship has been created based on the relationship columns.
then in PBI I have a matrix like this
Name | Minimum Amount | Minimum amount date | Rate | Minimum amount in Local ccy |
Tom | 16 | 28/02/2024 | ||
Peter | 57 | 10/02/2024 | ||
Sam | 457 | 16/01/2024 |
What I need to find is the rate when the minimum amount occured and then get the amount in local ccy.
For the rate I have tried the following but doesn't work:
Hi, @TheBaz_57
Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!
Best Regards
Yongkang Hua
@TheBaz_57 If the two tables are related you should just have to do an aggregation on each column and multiply them together. Maybe I'm not understanding something but hard to test as the sample data doesn't include the related rows for the currency conversion.
why my tables look so crappy 🙄
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
62 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |