Forum Discussion
Convert Sales from local currency to Dollar with missing dates
Hello,
i am new to power BI and would need some help with the below.
when the user click on the dollar slicer we need to convert our sales from local currency to Dollar currency based on the sales date.
to make it simple i have the following tables:
- Date: normal calendar table which has column date
- Sales: has sales value [Sold Value] and sales date [Posting Date]
- Currency table: has [Currency Code], [Starting Date] and [Rate], this table has missing dates (for example holidays)
we managed to create it and it's calculating the rates fine and if the user doesn't choose the slicer it displays them in local currency, however in days where there's no exchage rate it giving infinity.
i understand i need to get the rate where 'Currency Rates'[Starting Date] <= 'ITEM-STOERE-SALES'[Posting Date] but how?
appreciating your help
hello, i ended up repopulating the exchange table with missing dates, with rate of missing dates taking the value of previous date. the exchange table looks like this now:
with tmp(plant_date) as
(
select cast('20210101' as datetime)
union all
select plant_date + 1
from tmp
where plant_date < '20301231'
)select 'US2' as [Currency Code],dt as [Starting Date],max([Relational Exch_ Rate Amount]) over (partition by grp) [Relational Exch_ Rate Amount]
from
(
select coalesce([Starting Date],plant_date) dt,[Relational Exch_ Rate Amount],
SUM(CASE WHEN [Relational Exch_ Rate Amount] IS NOT NULL THEN 1 ELSE 0 END) OVER(
ORDER BY plant_date
ROWS UNBOUNDED PRECEDING) grp
from tmp left join (select [Currency Code],[Starting Date],[Relational Exch_ Rate Amount]
from Till.dbo.[UCCM$Currency Exchange Rate] where [Currency Code]='US2')
#t on tmp.plant_date=#t.[Starting Date]
) as der
option (maxrecursion 0)
3 Replies
hello, i ended up repopulating the exchange table with missing dates, with rate of missing dates taking the value of previous date. the exchange table looks like this now:
with tmp(plant_date) as
(
select cast('20210101' as datetime)
union all
select plant_date + 1
from tmp
where plant_date < '20301231'
)select 'US2' as [Currency Code],dt as [Starting Date],max([Relational Exch_ Rate Amount]) over (partition by grp) [Relational Exch_ Rate Amount]
from
(
select coalesce([Starting Date],plant_date) dt,[Relational Exch_ Rate Amount],
SUM(CASE WHEN [Relational Exch_ Rate Amount] IS NOT NULL THEN 1 ELSE 0 END) OVER(
ORDER BY plant_date
ROWS UNBOUNDED PRECEDING) grp
from tmp left join (select [Currency Code],[Starting Date],[Relational Exch_ Rate Amount]
from Till.dbo.[UCCM$Currency Exchange Rate] where [Currency Code]='US2')
#t on tmp.plant_date=#t.[Starting Date]
) as der
option (maxrecursion 0)- lbendlin
Super User
Usually you have monthly exchange rates and you create a "CurrencyYearMonth" composite key that you then use to link to your fact table. If your exchange rates have start dates then this becomes a lot more complex.
Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - Manoj_Nair
Solution Supplier
zjdaher - try this link where I've given the solution to the problem in my earlier post. Let me know if you still need any further clarification.
If my post helps you to find solution would be happy if you could mark my post as a solution and give it a thumbs up
Best regards
Manoj Nair
Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/
