Merging monthly data with daily data - best way?

[Anonymous]

I have a PBI report that ive taken on from someone else and an issue has been found so im not building this from scratch.

What i need to do is is create a relationship between daily and monthly data.

Exchange rate is monthly 

- Sep-2021, GBP, 0.7

- Sep-2021, USD, 1.1

- Oct-2021, GBP, 0.75

Fact table data is daily

-01-Sep-2021 - 20.3

there is no join between currency. the end user selects 'GBP' and the month total is multiplied by 0.7 for sept-2021

thus this creates a many to many relationship at monthly level

i see a 2 ways to do this but i am unsure of performance

-expand the core currency table to daily, either in mesaure or the raw data

-summarize the fact table to monthly in this specific measure 

can anyone help?

[v-luwang-msft]

Hi @Anonymous ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[amitchandak]

@Anonymous , If you have only or 2 currency, we usually create new column my using month year and currency join.

GBP Rate =

Maxx(filter(Rate,Table[Month] = Rate[Month] && Rate[Curr] = "GBP" ) , Rate[Monthly])

In the above we do create column or var make a sure month or date matches

But you want slicer of currency then we need to do it run time using measure

https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
https://radacad.com/currency-converter-power-bi-function-part-1
https://www.youtube.com/watch?v=Q4hF4CwJsFEhttps://radacad.com/currency-exchange-rate-conversion-in-power-bi-with-live-rates-part-2

[Anonymous]

hi, thanks for your reply.

yes there are many currencies. the links you supplied look representative of my problem, i will spend a bit of time looking at them to see if they help.

My problem is this

• Data in a single currency, report with multiple currencies

which looks to be directly addressed in the links

Thanks