Exchange rates for the weekend

[heldus]

Hello everyone,

I´m working right now on a Currency Conversion. This works so far and i can change several currencies based on the change rates into the home currency EURO, also related to the specific date.

My problem right now is that i have all the exchange rates for all the different currencies i want to convert, but the exchange rates are just available from Monday- Friday. 

In my case it is now possible that there are values i have to convert which are created on a weekend. For Example a Spare Part which has been sold on Saturday. Unfortunately with my current conversion i just cover the Values which are created between monday-friday because the Conversion is based on the Date.

I tried several things to get this Issue solved but nothing worked. 

So my Question now is there any possibility that i can use the last exisiting exchange rate ? For example when there is a Spare part sold on Saturday it just takes the exchange rate from Friday? 

I tried it with the function Lastnonblank but i was not able to get it done with this function.

Below you can see my current conversion which is working except for the weekends because of the missing exchange rates.

SalesAmountSpareParts = SUMX(SpareParts;
                 Divide(SpareParts[Amount];
                    LOOKUPVALUE(ExchangeRates[ExchangeRate];ExchangeRates[Date];SpareParts[Date];                          
                  ExchangeRates[Currency];SpareParts[Currency])))                       
  
+                        Calculate(Sum(SpareParts[Amount]);
                                    FILTER(SpareParts;SpareParts[Currency]="EUR"))

[Anonymous · Accepted solution]

@heldus  - 

In Power Query, you can do the following:

1. Right Outer Join with your Date table. This will add dates that don't exist yet in your exchange rate table.

2. Order by your new date column.

3. Fill values downwards.

4. Delete your old date column.

Cheers!

Nathan

[Irit]

Hi Nathan,

How do you fill values downwards? Manualy? 

Thanks

[heldus]

Hi @Anonymous ,

thank you for your answer.

I tried it and it works, but do you know how i can handle this if I have tables like the currency rate which get updated with the new rate ervery day?

I think it doesn´t fill up the new blank rows automatically ?

Best regards 

[Anonymous]

@heldus - If your data source is updated, it should come through and work when refreshed. Please share your pbix if it doesn't work.

Cheers!

Nathan

[heldus]

@Anonymous 

Thank you for your help.