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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors