Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 a matrix:

 

Power_BI_Adapt_1-1669715620903.png

 

Built by:

Power_BI_Adapt_2-1669715964375.png

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

 

 

2 ACCEPTED SOLUTIONS

@Anonymous 

 

Another mistake is that the DATE function is

DATE(year,month,day)

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

View solution in original post

Anonymous
Not applicable

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

View solution in original post

12 REPLIES 12
Anonymous
Not applicable

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
Not applicable

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
Solution Sage
Solution Sage

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
Not applicable

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'?

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
Not applicable

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

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
Not applicable

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!
 
Power_BI_Adapt_0-1669721693437.png

 

 

My bad...

 

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

@Anonymous 

 

Another mistake is that the DATE function is

DATE(year,month,day)

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

Anonymous
Not applicable

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
Not applicable

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.