Forum Discussion
Creating calculated measure for currency conversion, rate dependant on start and end date
Take a look at the pbix file attached below. I did some work in PQ so be sure to changet the FileLocation parameter to where those two csv files were.
The CurrencyExpanded table is where you want to look:
*Remove the time part from the date/time column. If you need time always best to store that separately from your date column. Improves performance
* I added a custom column with this code:
List.Dates ( [StartDate], Duration.Days( [NextStartDate] - [StartDate] ), #duration( 1, 0,0,0 ) )*This will produce a list of all the dates that fall between the StartDate and NextStart date
*Expand that list out to rows, and you no longer have any missing data.
*Remove all the intermediate columns as no longer needed
*Go to the Opportunity table. I merged this table with the CurrencyExpanded table. It is merged on CurrencyISOCode and ClosedDate. Since we expanded out the rate table, it will always have a value. Except if the currency is USD, so there's anotehr column that if the currency is USD give us 1 else give us the currency rate.
*Remove all the other columns
* Load all this. Then can write a simple measure of:
Total Sales in USD = SUMX( opportunity, opportunity[Correct Rate] * opportunity[License_Revenue__c] )
*I added in a Date table since you dont want to filter your Fact tables directly. So with dates on rows and the above measure in values:
it looks worse than it is. Just step through all the steps in PowerQuery and should make sense. Though I should say this technique ( normalizing the rate data into your fact table) will only really work for one currency. If you wanted to see sales in various currencies that would require some more complex dax. Could be done, but definitely more complex
Here's the pbix file:
https://1drv.ms/u/s!Amqd8ArUSwDS0z62HvrVl_SS-bLm
Hope this helps
Can you upload some sample data?
Thanks for the response Nick, looks like after getting time to myself and not having interruptions in my office I did indeed need the lookup value and I had used it incorrectly. I do need to validate that the info is correct. I think I was mulling trying to get the date the revenue opportunity closed being between the two dates in the currency table rather than just going by the start date. Now if there is more than a month between the currency rate changes then I do not believe the below will work.
License Revenue_c (converted) = SUMX ( Opportunity, IF ( Opportunity[CurrencyIsoCode] = "USD", Opportunity[License_Revenue__c], Opportunity[License_Revenue__c] * ( 1/ LOOKUPVALUE ( 'Dated Conversion Rate'[ConversionRate], 'Dated Conversion Rate'[IsoCode], Opportunity[CurrencyIsoCode], 'Dated Conversion Rate'[StartDate].[Year], Opportunity[CloseDate].[Year], 'Dated Conversion Rate'[StartDate].[MonthNo], Opportunity[CloseDate].[MonthNo] ) ) ) )I do have some clean sample data here https://1drv.ms/f/s!AmcxiOv-IfhZh8ZfO37cUzfYg1TLRA
So welcome a more elegant solution
Take a look at the pbix file attached below. I did some work in PQ so be sure to changet the FileLocation parameter to where those two csv files were.
The CurrencyExpanded table is where you want to look:
*Remove the time part from the date/time column. If you need time always best to store that separately from your date column. Improves performance
* I added a custom column with this code:
List.Dates ( [StartDate], Duration.Days( [NextStartDate] - [StartDate] ), #duration( 1, 0,0,0 ) )*This will produce a list of all the dates that fall between the StartDate and NextStart date
*Expand that list out to rows, and you no longer have any missing data.
*Remove all the intermediate columns as no longer needed
*Go to the Opportunity table. I merged this table with the CurrencyExpanded table. It is merged on CurrencyISOCode and ClosedDate. Since we expanded out the rate table, it will always have a value. Except if the currency is USD, so there's anotehr column that if the currency is USD give us 1 else give us the currency rate.
*Remove all the other columns
* Load all this. Then can write a simple measure of:
Total Sales in USD = SUMX( opportunity, opportunity[Correct Rate] * opportunity[License_Revenue__c] )
*I added in a Date table since you dont want to filter your Fact tables directly. So with dates on rows and the above measure in values:
it looks worse than it is. Just step through all the steps in PowerQuery and should make sense. Though I should say this technique ( normalizing the rate data into your fact table) will only really work for one currency. If you wanted to see sales in various currencies that would require some more complex dax. Could be done, but definitely more complex
Here's the pbix file:
https://1drv.ms/u/s!Amqd8ArUSwDS0z62HvrVl_SS-bLm
Hope this helps
