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...
  • 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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Willborn
        Advocate III
        9 years ago

        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