Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors