Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have two tables
1. Exchanage Rate (Contains Exchange rate of each quarter from Base currency USD)
2. Sales Detail (Contains sales detail in Local currency and may contain dates which are not in the exchange rate table)
Apart from this I have a Currency Dropdown on page.
My challange is by changing the currency I need to show the sales amount in specific selected currency.
For which I have to pick exchanage rate according to sales date. Here the twist is sales date might be possible not present in Exchange Rate table. so in such situation i have to pick latest exchange rate after sales date.
I appreciate your valuable inputs to create a measure.
Solved! Go to Solution.
Hi @Indiandotnet ,
You may create measure like DAX below.
Matched Exchange Rate= MAXX(TOPN(1,FILTER('Exchange Rate Table', 'Exchange Rate Table'[Date]<=MAX('Sales Detail'[Sales Date])),'Exchange Rate Table'[Date], DESC),'Exchange Rate Table'[Exchange Rate])
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Indiandotnet ,
Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.
Best regards
Amy
Hi @Indiandotnet ,
You may create measure like DAX below.
Matched Exchange Rate= MAXX(TOPN(1,FILTER('Exchange Rate Table', 'Exchange Rate Table'[Date]<=MAX('Sales Detail'[Sales Date])),'Exchange Rate Table'[Date], DESC),'Exchange Rate Table'[Exchange Rate])
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can add the below as a column to yor Sales table and hide the Rate table
Column = VAR _Currency = TREATAS( { Sales[Base Currency] }, Rate[Currency] ) VAR _SalesDate = Sales[Sales Date] RETURN CALCULATE( MAX( Rate[Exchange Rate] ), _Currency, Rate[Date] <= _SalesDate )
Actually I don't need calculated column. I need measure only because the value will change as per the selection of currency dropdown.
Perhaps something like:
Measure = VAR __salesDate = MAX([Sales Date]) VAR __amount = MAX([Amount]) VAR __exchangeDate = MAXX(FILTER('Exchange Rates',[Exchang Rate] <= __salesDate),[Date]) // assumes exchange rate table filtered by slicer RETURN MAXX(FILTER('Exchange Rates',[Date] = __exchangeDate),[Exchange Rate])
Several assumptions made in there.
Thanks for the reply.
But it will not resolve the problem.
I need the exchange rate again each sales date. Now , the issue is there is no direct relationship between Exchange Rate table and Sales table. As Sales table may contain Dates which are not exists in Exchange Rate table.
Here is the logic
1. If Exchange Date of Exchange rate and sales date of sales table match then pic the exchange rate of that particular row .
2. If exchange rate not exists for sales date then pic exchange rate of exchange date just below the sales date.
I hope it is more clear.
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |