Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I do have three tables,
1. FCRATE - Multiple fields, contain different currencies, dates, and conversion rates for other currencies
2. Project cost - Three fields - Project name, project transaction amount ($), Transaction date
3. Date table
The relationship between tables is by Date. FCRATE & Project Cost table connected to Date table.
I have a visual showing the Project cost for each project. Also have a filter to see Project cost by date, month or year.
Now, I need a new slicer, that allows users to select the desired currency from a drop-down. Once the user selects a currency, all amounts in visuals need to convert to that currency based on the currency conversion rate as of the selected date from the filter.
Other conditions,
if the user selected a desired month or year - then the average currency rate for that period should be calculated and converted to the project cost amount based on that.
My FCRATE table,
Can someone guide me in creating key measures and slicers for this?
Hi @ravjay
Are the rates available for every single date? Meaning if I select any date from the date table there will be a corrosponding exchange rate for all of the currencies in the FCRATE table?
The END_DATE of the FCRATE table seems to be of a text data type. Be aware that this needs to be of a DateTime data type.
If above two points are confirmed and the FCRATE[CODE] is placed as a slicer then project cost measure can be amended as
Project Cost Amount =
SUM ( 'Project Cost'[Transaction Amount] ) * SELECTEDVALUE ( FCRATE[USD], 1 )
Yes, rates are available for all days.
- Each currency has conversion rates for all other currencies for each date.
- END_DATE field changed to date format
Please also note that the structure of the FCRATE Table is as follows,
Let's assume transaction amounts are in the XOF rate.
So, if I select GBP on the slicer and the date 30/03/2020 from the date filter, What I'm expecting is the transaction amount will be changed from XOF to reflect the GBP amount for that date.
For example,
1. if the transaction amount is 10,000 in XOF, once the slicer is set to GBP & date filter is applied as 30/03/2020, the transaction amount should be changed to 13.5689
2. Let's assume transaction amounts are in the CAD rate.
if the transaction amount is 10,000 in CAD, once the slicer is set to USD & date filter is applied as 30/03/2020, the transaction amount should be changed to 7,058.5172,
likewise, the transaction amount needs to change based on the transaction currency, and the selected currency on slicer & date.
@ravjay
My previous reply solution was based on the assumption that the Date table filters both 'Project cost' and FCRATE tables. If this is the case then I don't see why it shouldn't work.
In case a range of dates is selected, the following should work
Project Cost Amount =
SUMX (
VALUES ( 'Date'[Date] ),
CALCULATE (
SUM ( 'Project Cost'[Transaction Amount] ) * SELECTEDVALUE ( FCRATE[USD], 1 )
)
)
Hi @ravjay ,
Could you please provide your pbix file without privacy information and desired output with more details?
How to Get Your Question Answered Quickly
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
22 | |
21 | |
17 | |
14 | |
11 |
User | Count |
---|---|
42 | |
35 | |
25 | |
24 | |
22 |