cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Willborn Member
Member

Calculating Values with FX Rates from different periods

Good morning

 

I'm struggling with finding a solution for calculating with currency rates – so far tried almost everything, but I guess, this can be done with DAX measures only.

Would be great, if someone has an approach for me.

 

I have a table with values in CHF, which is always filtered to YTD. Our controllers want to calculate the deviation of the value of the previous year to the actual year using local currency.

The problem I had so far is, that the origin system is calculating with average rates in local currency. I get this average in my currency file.

 

Table with values is 'COG', the table for currency is 'FX' and I use a date table 'DATE' where our FY is available (April-March). For the calculation, I need to take the average rate for each currency from the "last month" only, and multiply this with the same value I had in the same month on year ago. This means, I have to calculate the following:

 

'COG' [Value] /   'FX' "last month of actual FY"   *  'FX' "last month of previous FY"

 

The problem I have:

When the filter on 'COG' Value is on April-November 2016, I need to calculate the sum, divide it with FX value of November 2016 multiplied with November 2015 – but as soon as I filter for April-November, or YTD over the 'DATE' table', also the 'FX' table is filtered. So I disconnected the 'FX' table from 'DATE', made a copy and renamed it to 'FXACT' and 'FXPY'

 

'COG' [Value] /   'FX' "November 2016"   *  'FX' "November 2015"

 

Now I have to pack this into a DAX formula – guess I need to use "previousmonth" function and "previousmonth" in combination with "sameperiodelastyear" – but I don't know how…

 

Does someone can help me with this?

 

Regards Patrick

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculating Values with FX Rates from different periods

Hi @Willborn,

 

>>Is there some possibility to filter the table "-13 month" from today?

Yes, it is possible. You can take a look at below formula:

 

#"Filtered Rows" = Table.SelectRows(#"Previous StepName", each [Filter ColumnName] >= Date.AddMonths(Date.From(DateTime.LocalNow()),-13) and [Filter ColumnName] <= Date.From(DateTime.LocalNow()))

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

8 REPLIES 8
Highlighted
Willborn Member
Member

Re: Calculating Values with FX Rates from different periods

Hi there

 

I actually could create a solution with two FX Tables manually filtered - and some measures. Probably not the easiest and most professional approach, but it works.

 

Now this is static - as I just filtered  one FX table to Sept 2016 and the other one to Sept 2015 - then made a table join.

The FX currently filtering to Sept 2016 can be filtered to "last month", so this will be dynamic, but I cannot find a solution to filter the 2nd FX table to "last month, last year" using the filter in PBI Desktop...

 

Any approach for this?

 

Thanks and regards, Patrick

Community Support Team
Community Support Team

Re: Calculating Values with FX Rates from different periods

Hi @Willborn,

 

Can you share some sample data to test?

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Willborn Member
Member

Re: Calculating Values with FX Rates from different periods

Hi Xiaoxin

 

I will try to prepare some example data - actually, the data model is pretty big. So far I got a manual solution (manually filter the FX table by date) but I need to filter the table in Power BI Desktop to "last month" but within the last year. So, when checking the data in October 2016 - the table should be filtered to September 2015.

 

Best regards, Patrick

Community Support Team
Community Support Team

Re: Calculating Values with FX Rates from different periods

Hi @Willborn,

 

You can try to use below table formulas:

 

Use today function to filter data:
Last Year's records =
CALCULATETABLE(Table,FILTER(ALL(Table),[Date]<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)-1))


Use lastdate function to filter data:
Last Year's records=
var last= LASTDATE(Table[Date])
return
CALCULATETABLE(Table,FILTER(ALL(Table),[Date]<=DATE(YEAR(last)-1,MONTH(LASTDATE(last)),1)-1))

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Willborn Member
Member

Re: Calculating Values with FX Rates from different periods

Hi Xiaoxin

 

I couldn't get a result with the above DAX for calculated tables. Actually, I need to do this in the query editor already. My solution is working, but only when I change the filter each month manually.

 

Below the last step in 'FX ACT' table, where I filter for previous month ( November 2016):

 

    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns2", each Date.IsInPreviousMonth([Date]))

 

Below the two last steps in 'FX PY' table, where I filter for previous year - but "November" is static:

 

    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns2", each Date.IsInPreviousYear([Date])),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Month] = "November"))

 

Is there any way, I can get "November" as dynamic value, so I have always the last month?

 

Regards Patrick

Willborn Member
Member

Re: Calculating Values with FX Rates from different periods

Is there some possibility to filter the table "-13 month" from today?

 

Regards Patrick

Community Support Team
Community Support Team

Re: Calculating Values with FX Rates from different periods

Hi @Willborn,

 

>>Is there some possibility to filter the table "-13 month" from today?

Yes, it is possible. You can take a look at below formula:

 

#"Filtered Rows" = Table.SelectRows(#"Previous StepName", each [Filter ColumnName] >= Date.AddMonths(Date.From(DateTime.LocalNow()),-13) and [Filter ColumnName] <= Date.From(DateTime.LocalNow()))

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Willborn Member
Member

Re: Calculating Values with FX Rates from different periods

Hello Xiaoxin

 

Many thanks for your help - I got it and it's working. Actualllly, as I need only Last Month in Previous Year - I've changed the formula to:

 

= Table.SelectRows(#"Renamed Columns2", each [Date] >= Date.AddMonths(Date.From(DateTime.LocalNow()),-14) and [Date] <= Date.AddMonths(Date.From(DateTime.LocalNow()),-13))

 

Thanks and best regards!

Patrick

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 158 members 1,726 guests
Please welcome our newest community members: