Forum Discussion

Willborn's avatar
Willborn
Advocate III
9 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    9 years ago

    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

8 Replies

  • Willborn's avatar
    Willborn
    Advocate III
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Willborn,

       

      Can you share some sample data to test?

       

      Regards,
      Xiaoxin Sheng

      • Willborn's avatar
        Willborn
        Advocate III
        9 years ago

        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