Add measure calculating difference two other measxures / columns to be used in matrix

[Anonymous]

Dear All,

I try to reproduce some suggestions made here on the forum to build a comparison column, containing the difference of two other columns in a matrix. It concerns the following example of a matrix:

Built by:

Translate:

* Rijen = rows

* Omschrijving = description

* Kolommen = columns

* Waarde = Values

* Historische AIP = historical price

* Diff AIP = difference price

I built already a measure called 'Diff AIP' to compare two columns (historical price for 1st March and 1st November), but there is no result: 

Diff AIP =

CALCULATE (

    [Historische AIP],

    FILTER (

        ALL ( 'Dim Datum'[Date Actual] ),

        'Dim Datum'[Date Actual] = 1-11-2022

    )

)

    - CALCULATE (

        [Historische AIP],

        FILTER (

           ALL ( 'Dim Datum'[Date Actual] ),

            'Dim Datum'[Date Actual] = 1-3-2022

        )

    )

Thanks in advance for any suggestion how to solve this.

Kind regards

[JorgePinho · Accepted solution]

@Anonymous 

Another mistake is that the DATE function is

DATE(year,month,day)

So correct it to: DATE(__CurrentYear,__CurrentMonth,1)

[Anonymous]

Thanks, all corrections have been done. Unfortunatelky the first column to be defined in the DATEADD function doesn't still work. It says with column '__Date1' (marked in red below): 'Parameter is not the correct type'

So i am doubting whether the correct column name or format is being recognised. 

Diff AIP_6 =

VAR __CurrentMonth = MONTH(TODAY())

VAR __CurrentYear = YEAR(TODAY())

VAR __Date1 = DATE(__CurrentYear,__CurrentMonth,1)

VAR __Date2 = DATEADD(__Date1, -8, MONTH)

RETURN

CALCULATE (

    [Historische AIP],

    FILTER (

        ALL ( 'Dim Datum'[Date Actual] ),

        'Dim Datum'[Date Actual] = __Date1

    )

)

    - CALCULATE (

        [Historische AIP],

        FILTER (

           ALL ( 'Dim Datum'[Date Actual] ),

            'Dim Datum'[Date Actual] = __Date2

        )

    )

[Anonymous]

Thanks! This is working properly.

Just wondering: How could I make this more dynamically. This month I compare prices of 1st November with 1st March. Next month I will compare 1st December with 1st April (or 1st November). How can I make it dynamically, without adjusting every monthe the measure 'Diff AIP'?

[JorgePinho]

Hello @Anonymous !

You should replace " 1-11-2022" by DATE(2022,11,1) and 1-3-2022 by DATE(2022,3,1). See if it solves the issue 🙂

[Anonymous]

Just wondering: How could I make this more dynamically. This month I compare prices of 1st November with 1st March. Next month I will compare 1st December with 1st April (or 1st November). How can I make it dynamically, without adjusting every monthe the measure 'Diff AIP'?

[JorgePinho]

You can do something like this:

VAR __CurrentMonth = MONTH(TODAY())

VAR __CurrentYear = YEAR(TODAY())

VAR __Date1 = DATE(1,__CurrentMonth,__CurrentYear)

VAR __Date2 = DATEDADD(Date1, -8, MONTHS)

And then use the variables in the current DAX forumula.

You can even go further by creating a What If parameter to make the number of months to subtract on DATEADD dynamic to the user.

[Anonymous]

Thanks a lot!

Since I am not that familiar with the VAR fuctionality, how would this fit into beneath measure?

Diff AIP =

CALCULATE (

    [Historische AIP],

    FILTER (

        ALL ( 'Dim Datum'[Date Actual] ),

        'Dim Datum'[Date Actual] = DATE(2022,11,1)

    )

)

    - CALCULATE (

        [Historische AIP],

        FILTER (

           ALL ( 'Dim Datum'[Date Actual] ),

            'Dim Datum'[Date Actual] = DATE(2022,3,1)

        )

    )

[JorgePinho]

With VAR you declare variables that you can then call in the code.

It would look something like this:

Diff AIP =

VAR __CurrentMonth = MONTH(TODAY())

VAR __CurrentYear = YEAR(TODAY())

VAR __Date1 = DATE(1,__CurrentMonth,__CurrentYear)

VAR __Date2 = DATEDADD(Date1, -8, MONTHS)

RETURN

CALCULATE (

    [Historische AIP],

    FILTER (

        ALL ( 'Dim Datum'[Date Actual] ),

        'Dim Datum'[Date Actual] = __Date1

    )

)

    - CALCULATE (

        [Historische AIP],

        FILTER (

           ALL ( 'Dim Datum'[Date Actual] ),

            'Dim Datum'[Date Actual] = __Date2

        )

    )

If my suggestions above solved your issue don't forget to mark it as a solution and give kudos 🙂

[Anonymous]

Thanks a lot!

I am almost there....

Within the variable   

VAR __Date2 = DATEDADD(Date1, -8, MONTHS), i got some errors.

Two I could solve:  DATEDAD= DATEADD and MONTHS is being accepted as MONTH, however the error comment says 'Cannot find name 'Date1'.  I also tried already name '__Date1', but without a result. Any idea..?

Thanks!

[JorgePinho]

My bad...

Is it MONTH and not MONTHS and it is __Date1 and not Date1.

[JorgePinho · Accepted solution]

@Anonymous 

Another mistake is that the DATE function is

DATE(year,month,day)

So correct it to: DATE(__CurrentYear,__CurrentMonth,1)

[Anonymous]

Thanks, all corrections have been done. Unfortunatelky the first column to be defined in the DATEADD function doesn't still work. It says with column '__Date1' (marked in red below): 'Parameter is not the correct type'

So i am doubting whether the correct column name or format is being recognised. 

Diff AIP_6 =

VAR __CurrentMonth = MONTH(TODAY())

VAR __CurrentYear = YEAR(TODAY())

VAR __Date1 = DATE(__CurrentYear,__CurrentMonth,1)

VAR __Date2 = DATEADD(__Date1, -8, MONTH)

RETURN

CALCULATE (

    [Historische AIP],

    FILTER (

        ALL ( 'Dim Datum'[Date Actual] ),

        'Dim Datum'[Date Actual] = __Date1

    )

)

    - CALCULATE (

        [Historische AIP],

        FILTER (

           ALL ( 'Dim Datum'[Date Actual] ),

            'Dim Datum'[Date Actual] = __Date2

        )

    )

[Anonymous · Accepted solution]

I just found the soultion:

Instead of

VAR __Date2 = DATEADD(__Date1, -8, MONTH)

It is:

VAR __Date2 = DATEADD('Dim Datum'[Date Actual], -8, MONTH)

Complete measure is:

Diff AIP_6 =

VAR __CurrentMonth = MONTH(TODAY())

VAR __CurrentYear = YEAR(TODAY())

VAR __Date1 = DATE(__CurrentYear,__CurrentMonth,1)

VAR __Date2 = DATEADD('Dim Datum'[Date Actual], -8, MONTH)

RETURN

CALCULATE (

    [Historische AIP],

    FILTER (

        ALL ( 'Dim Datum'[Date Actual] ),

        'Dim Datum'[Date Actual] = __Date1

    )

)

    - CALCULATE (

        [Historische AIP],

        FILTER (

           ALL ( 'Dim Datum'[Date Actual] ),

            'Dim Datum'[Date Actual] = __Date2

        )

    )