Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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...
Need Help
  • JorgePinho's avatar
    JorgePinho
    3 years ago

    Anonymous 

     

    Another mistake is that the DATE function is

    DATE(year,month,day)

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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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
            )
        )
JorgePinho's avatar
JorgePinho
Icon for Solution Sage rankSolution Sage
3 years ago

My bad...

 

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

JorgePinho's avatar
JorgePinho
Icon for Solution Sage rankSolution Sage
3 years ago

Anonymous 

 

Another mistake is that the DATE function is

DATE(year,month,day)

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    Anonymous
    Not applicable
    3 years ago

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