Measures for un related table

[Indiandotnet]

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.

[v-xicai]

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

[v-xicai · Accepted 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.

[Mariusz]

Hi @Indiandotnet 

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
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Indiandotnet]

Actually I don't need calculated column. I need measure only because the value will change as per the selection of  currency dropdown.

[Greg_Deckler]

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.

[Indiandotnet]

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.