Completing a date table with currencies

[Juleeees]

We integrate a currency conversion in Power BI and have used Euro Stat for this. This works so far that we have all currency conversions for working days. Currently it looks like this: 

We would like to have a table where weekends and Public Holidays are included.

The idea was to take previous values. Whenever a value is missing, a previous value would have to be taken (idea was a loop, go back until value is filled in and then take this value).

For this, we need to add a complete date list + attribute (currency indicator). For example we need two rows for saturday(one for USD and one for CNY).

We have a full calendar in our PBI file that could be used for joints or whatever the idea is.

Thanks in advantage

[Juleeees]

Thanks for your help. 

This was an Example: We have 20+ Currencies, so this way would be too large for power bi. We need an directer solution

[v-jingzhang · Accepted solution]

Hi @Juleeees 

I have attached my sample file at bottom. Hope it helps. Prepare a full date table and attribute table in advance. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[HotChilli]

If you want to Merge you could make 2 copies of the date table and in each of them add a column for currency. In one USD and in the other CNY.

Then Append those tables together.

Then do the Merge. Left-join from this large table to currency table, join on date and currency.

That should get you a large table with no values for exchange rates on some dates. Replace those empty values with null.

If you then sort that table by currency AND date, I think you might get away with a 'Fill Down'.  I'm just visualising in my head at this stage.  See how you get on.