Adding new column and converting from AU to NZ dollar

[Troyvan]

Hi, 

I have a table with the following column - Account, Date and Value

The Account column contains AU and NZ

I want to create a new column and filter all values under AU and multiply by 1.11 to convert to NZ dollar. 

I'm thinking of just manually converting it for now by putting the exchange rate every month but I can't seem to come up with the right formula to add this column. If you can help me add this, that would be greatly appreciated. 

Or better yet, if anyone know how can I dynamically select values under AU account and convert to NZ, that would be epic. Thank you. 

Cheers,
Vanessa

[Ashish_Mathur]

Hi,

Share your actual table and the exchange rate table.

[Troyvan]

Hi @Ashish_Mathur , 

This is what my data looks like.  

Account	Finance Cost	Year - Month	Value	Date
AU	NZ Software	Jan - 22	0	1/01/2022
AU	NZ Software	Feb - 22	0	1/02/2022
AU	NZ Software	Mar - 22	0	1/03/2022
AU	NZ Software	Apr - 22	0	1/04/2022
AU	NZ Software	May - 22	0	1/05/2022
AU	NZ Software	Jun - 22	0	1/06/2022
AU	NZ Software	Jul - 22	0	1/07/2022
AU	AU Discretionary	Aug - 22	0	1/08/2022
AU	AU Discretionary	Sep - 22	0	1/09/2022
AU	AU Discretionary	Oct - 22	0	1/10/2022
AU	AU Software	Nov - 22	0	1/11/2022
AU	AU Software	Dec - 22	0	1/12/2022
AU	AU Software	Jan - 23	0	1/01/2023
AU	AU Software	Feb - 23	0	1/02/2023
AU	AU Software	Mar - 23	0	1/03/2023
AU	AU Software	Apr - 23	0	1/04/2023
AU	AU Discretionary	May - 23	0	1/05/2023
AU	AU Discretionary	Jun - 23	0	1/06/2023
AU	AU Discretionary	Aug - 23	0	1/08/2023
AU	NZ Discretionary	Sep - 23	0	1/09/2023
AU	NZ Discretionary	Oct - 23	0	1/10/2023
AU	NZ Discretionary	Nov - 23	0	1/11/2023
AU	NZ Discretionary	Dec - 23	0	1/12/2023

My exchange  table looks like this. 

AUDNZD

1 AUD

1.11012 NZD

I got this exchange rate table from this site below but I'm not sure if this will update in my power Bi dynamically and how I can use it to dyanmically convert row in AUD to NZD?

1 AUD to NZD - Australian Dollars to New Zealand Dollars Exchange Rate (xe.com)

Your help is greatly appreciated. Thanks! 

Cheers,
Troy

[Ashish_Mathur]

Hi,

Why are there 0's in the Value column?  In the exchange rate table shouldn't there be a Date column.  Share complete information and show the expected result very clearly

[Troyvan]

Hi @Ashish_Mathur 

I want to be  able to add a new column  $NZD = If( Account ="AU", Value * NZD Exchange Rate - this should dynamically update with the current exchange rate), similar to this: 

Account	Finance Cost	Date	Year - Month	Value	$NZD
NZ	NZ Software	1/01/2022	Jan - 22	1000	1000
NZ	NZ Software	1/02/2022	Feb - 22	1000	1000
AU	AU Discretionary	1/10/2022	Oct - 22	1000	1000
AU	AU Software	1/11/2022	Nov - 22	1000	1110
AU	AU Software	1/03/2023	Mar - 23	1000	1110
AU	AU Software	1/04/2023	Apr - 23	1000	1110
AU	AU Discretionary	1/08/2023	Aug - 23	1000	1110
NZ	NZ Discretionary	1/09/2023	Sep - 23	1000	1110

My exchange rate table from  https://www.xe.com/currencyconverter/convert/?Amount=1&From=AUD&To=NZD is this: I don't know how to add date column from here 

I imported table from the web, would that mean it should update automatically? How can I use it? So sorry I'm just really starting to learn Power Bi. Really appreciate your help. Thanks! 

Cheers,

Troy

[tamerj1 · Accepted solution]

Hi @Troyvan 
You can create a new calculated column using dax
New Column >
NZ Value = IF ( TableName[Account] = "AU", TableName[Value] * 1.11, TableName[Value] )

[Troyvan]

Thanks @tamerj1 

Instead of manually changing the exchange rate value to multiply it to to convert to $NZ, any advise on how I can do it dynamically? Like multiplying it to updated exchange rate on any website? Thanks! 

Cheers,

Troy

[tamerj1]

@Troyvan 

You can add a date column to the exchange table and link it to the date table. New record with new date can be added to the table. But your idea sound much more interesting I would also love to know if this possible.