Forum Discussion
Merging monthly data with daily data - best way?
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?
3 Replies
- amitchandak
Super User
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-2hi, 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
- v-luwang-msft
Community Support
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
