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

    • Willborn's avatar
      Willborn
      Advocate III
      9 years ago

      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