Creating calculated measure for currency conversion, rate dependant on start and end date

[mabegg]

I have a table with a list of currency rates that change depending on the period, I need to that use the correct rate to convert the license amount in another table based on the close date. Neither table is related, there would be a many to many relationship say between isocodes in the two tables. I am just stumped here whether to userelationsship or lookupvalue but stuck on how to select the right conversion rate between the start and end date in the rate table 😞

Currency table is below

The other table has the revenue to be converted, if it is USD it is essentially rate = 1, otherwise rate * revenue based on closed date falling between start and end date in rate table.

Below is the revenue table

[Anonymous]

Can you upload some sample data?

[mabegg]

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

[Anonymous · Accepted 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