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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Power_BI_Adapt
Resolver I
Resolver I

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

@Power_BI_Adapt 

 

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

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
Power_BI_Adapt
Resolver I
Resolver I

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

 

Power_BI_Adapt
Resolver I
Resolver I

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 @Power_BI_Adapt !

 

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 🙂

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.

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 🙂

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.

@Power_BI_Adapt 

 

Another mistake is that the DATE function is

DATE(year,month,day)

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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