cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Juleeees
Frequent Visitor

Completing a date table with currencies

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: 

 

Juleeees_0-1669890060489.png

 

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

 

1 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. 

vjingzhang_0-1670320784058.png

 

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

View solution in original post

3 REPLIES 3
Juleeees
Frequent Visitor

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

Hi @Juleeees 

 

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

vjingzhang_0-1670320784058.png

 

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

HotChilli
Super User
Super User

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors