Difference between 2 columns in Matrix

[Manar]

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 column (Reporting Period), when I connect to the data source I only bring the latest 2 periods (Direct Query).   Could you please help me out.

[Zubair_Muhammad]

HI @Manar

Try a MEASURE along following lines

Measure =
CALCULATE ( SUM ( TableName[Rates] ), TableName[ReportingPeriod] = 201803 )
    - CALCULATE ( SUM ( TableName[Rates] ), TableName[ReportingPeriod] = 201802 )

[Anonymous]

What about if we have year more than 2 like 2015 , 2016 ,2017 ,2018 , 2019, 2020

[Manar]

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

@Manar

Hi,

Are these 2 MEASUREs?

CurrencyDim[CurrentReportingPeriod]

CurrencyDim[LastReportingPeriod]

[Zubair_Muhammad]

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]

@Zubair_Muhammad It tells me function Filter is not supported in this context in DirectQuery Mode.   😞 

Thank you though!

[Zubair_Muhammad · Accepted solution]

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]

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]

@bjsrm8 

What if you remove the filter function?

Diff =
CALCULATE ( SUM ( Original[Tax] ), Original[Period] = 3 )
    - CALCULATE ( SUM ( Original[Tax] ), Original[Period] = 2 )

[bjsrm8]

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

[Manar]

@Zubair_Muhammad Big Thank you!