Issue with calculating revenue because of exchange rates

[Anonymous]

Hi,

I am having an issue with finding the revenue, because some rows are not populated with the exchange rate number (because it is only entered on random dates).

So I have a long list of orders in Query 1(in US dollars) and Query 2 has exchange rates (to convert to CAD).

Query 1 looks like this:

And Query 2 looks like this. It has exchange rates and the random dates they were entered into the system.

For example, the first exchange rate listed (1.223) applies to all dates between 10/1/2019 and 11/6/2020, so until a new exchange rate is entered in the system.

I am trying to calculate revenue  (Revenue = Unit Price * Quantity * Exchange Rate) but since there is not an exchange rate for every day, only the day that the number was entered, I am having difficulties.

Thank you!

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Kindly check below results:

Pbix attached.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Kindly check below results:

Pbix attached.

[Anonymous]

@v-diye-msft 

Is there any way to do this in direct query?

Currently, I can not use MINX, EARLIER, or FILTER in the column.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Use the below measures:

Measure 2 = IF(ISBLANK(MINX(FILTER(ALL(Table_4),[Date]>MAX(Table_4[Date])),[Date])),DATE(9999,12,31),MINX(FILTER(ALL(Table_4),[Date]>MAX(Table_4[Date])),[Date]))

Measure 3 = MINX(FILTER('Table_4',MAX(Table_3[Date])>=[Date]&&MAX(Table_3[Date])<[Measure 2]),[Exchange Rate])

Revenue1 = MAX(Table_3[Quantity])*MAX(Table_3[UnitPrice])*[Measure 3]

[Greg_Deckler]

@Anonymous - Seems like you will need to create a measure to return the maximum date for the exchange rate that is less than the current date in your row. You can do this with MAXX using the correct FILTER. You could then use that to again use MAXX and FILTER to lookup the corresponding exchange rate. See this pattern:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434