Calculating Values with FX Rates from different periods

[Willborn]

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

[Willborn]

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

[v-shex-msft]

Hi @Willborn,

Can you share some sample data to test?

Regards,
Xiaoxin Sheng

[Willborn]

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

[v-shex-msft]

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

[Willborn]

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]

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

Regards Patrick

[v-shex-msft · Accepted solution]

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

[Willborn]

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