Forum Discussion

Manar's avatar
Manar
Helper II
8 years ago
Solved

Difference between 2 columns in Matrix

Hi, I have the following Matrix and I would like to add another column with the difference between the rates of the 2 different periods columns (201803 & 201802), these 2 columns are from the same co...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI Manar

     

    Try this one

     

    Measure =
CALCULATE (
    SUM ( ExchangeRatesFacts[ExchangeRate] ),
    FILTER (
        ALL ( ExchangeRatesFacts[ReportingPeriod] ),
        ExchangeRatesFacts[ReportingPeriod] = [CurrentReportingPeriod]
    )
)
    - CALCULATE (
        SUM ( ExchangeRatesFacts[ExchangeRate] ),
        FILTER (
            ALL ( ExchangeRatesFacts[ReportingPeriod] ),
            ExchangeRatesFacts[ReportingPeriod] = [LastReportingPeriod]
        )
    )
Manar's avatar
Manar
Helper II
8 years ago

Zubair_Muhammad

 

I tried using your formula just added a measure instead of reporting period since they change every month

 

Measure = CALCULATE ( SUM ( ExchangeRatesFacts[ExchangeRate] ), ExchangeRatesFacts[ReportingPeriod] = CurrencyDim[CurrentReportingPeriod] )
    - CALCULATE ( SUM ( ExchangeRatesFacts[ExchangeRate] ), ExchangeRatesFacts[ReportingPeriod] = CurrencyDim[LastReportingPeriod])

 

and it gives me an error saying a function Calculate has been used in a true/false expression that is used as a table filter expression, This is not allowed.

 

 

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Manar

 

Hi,

 

Are these 2 MEASUREs?

 

CurrencyDim[CurrentReportingPeriod]

CurrencyDim[LastReportingPeriod]

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    HI Manar

     

    Try this one

     

    Measure =
VAR CurrentRP = [CurrentReportingPeriod]
VAR LastRP = [LastReportingPeriod]
RETURN
    CALCULATE (
        SUM ( ExchangeRatesFacts[ExchangeRate] ),
        ExchangeRatesFacts[ReportingPeriod] = CurrentRP
    )
        - CALCULATE (
            SUM ( ExchangeRatesFacts[ExchangeRate] ),
            ExchangeRatesFacts[ReportingPeriod] = LastRP
        )
    • Manar's avatar
      Manar
      Helper II
      8 years ago

      Zubair_Muhammad It tells me function Filter is not supported in this context in DirectQuery Mode.   :( 

       

      Thank you though!

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        HI Manar

         

        Try this one

         

        Measure =
CALCULATE (
    SUM ( ExchangeRatesFacts[ExchangeRate] ),
    FILTER (
        ALL ( ExchangeRatesFacts[ReportingPeriod] ),
        ExchangeRatesFacts[ReportingPeriod] = [CurrentReportingPeriod]
    )
)
    - CALCULATE (
        SUM ( ExchangeRatesFacts[ExchangeRate] ),
        FILTER (
            ALL ( ExchangeRatesFacts[ReportingPeriod] ),
            ExchangeRatesFacts[ReportingPeriod] = [LastReportingPeriod]
        )
    )