cancel
Showing results for
Did you mean:
Frequent Visitor

## Measures for un related table

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Measures for un related table

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.

6 REPLIES 6
Super User

## Re: Measures for un related table

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])
```

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Super User

## Re: Measures for un related table

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.

Frequent Visitor

## Re: Measures for un related table

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.

Frequent Visitor

## Re: Measures for un related table

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

Community Support Team

## Re: Measures for un related table

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.

Community Support Team

## Re: Measures for un related table

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

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 365 members 3,832 guests
Recent signins: