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

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

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

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

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

        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!