Monthly currency, Daily sales - best way to join?

[Anonymous]

I have a question regarding efficiency 

 fact table of sales values in EUR

-daily grain

-1 currency

dimension currency table

-monthly grain

-many currencies

dimension date table

-daily

i need to report in these currencies.

Main question is,

-is it better to use LOOKUPVALUE (as you cant join 1 to many from currency to date with the different grains)?

or

-expand the currency table to daily so can join on date(1) to currency (many) and go from there?

thoughts?

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can refer the following links to configure your model and create the measures to get the expected result:

Currency conversion in Power BI reports

Dynamic currency conversion with DAX

💲 Learning to Convert Currencies with Power BI

1. Create relationships among sales, date and currency tables

2. Create measures to get the sales info base on different currencies

Best Regards

[amitchandak]

@Anonymous , I usually create a key column in both tables like

[Curr Code]|| "-" || [Month Year]

Month Year =  format([Date], "YYYYMM")

and join them 

If need I can get conversion in fact using related 

related(Rate[Conversion Rate])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8