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

    • Willborn's avatar
      Willborn
      Advocate III
      9 years ago

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

       

      Regards Patrick

      • Anonymous's avatar
        Anonymous
        Not applicable
        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