cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Indiandotnet Frequent Visitor
Frequent Visitor

Measures for un related table

111.png

 

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
Community Support Team

Re: Measures for un related table

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.

 

6 REPLIES 6
Super User
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])

Several assumptions made in there.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Measures for un related table

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.
Mariusz Repczynski

 

Indiandotnet Frequent Visitor
Frequent Visitor

Re: Measures for un related table

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.

Indiandotnet Frequent Visitor
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
Community Support Team

Re: Measures for un related table

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.

 

Community Support Team
Community Support Team

Re: Measures for un related table

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

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 365 members 3,832 guests
Please welcome our newest community members: