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 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]
        )
    )
  • bjsrm8's avatar
    bjsrm8
    Frequent Visitor
    7 years ago

    Hi Zubair_Muhammad ,

     

    Thanks for all of your insight in the forums. I have used your outline for my project but am unable to show any results. I have the same use case, where I need to get a variance between two months within a matrix. 

     

    Here is my modified code:

     

    Diff =
    CALCULATE (
    SUM ( Original[Tax] ),
    FILTER (
    ALL ( Original ),
    Original[Period] = 3
    )
    )
    - CALCULATE (
    SUM ( Original[Tax] ),
    FILTER (
    ALL ( Original ),
    Original[Period] = 2
    )
    )
     
    I'm trying to get a difference in tax amount by month. Here is a picture of my scenario. Can you please help me? There is no error, only no data populating in a matrix. 
     
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      7 years ago

      bjsrm8 

       

      What if you remove the filter function?

       

      Diff =
CALCULATE ( SUM ( Original[Tax] ), Original[Period] = 3 )
    - CALCULATE ( SUM ( Original[Tax] ), Original[Period] = 2 )
      • bjsrm8's avatar
        bjsrm8
        Frequent Visitor
        7 years ago

        Zubair_Muhammad 

         

        Still having the same issue with no data populating. I'm sure I'm missing something very easy but I can't figure it out. There are also no filters.