cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

Built by:

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

@Anonymous

Another mistake is that the DATE function is

DATE(year,month,day)

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

Anonymous
Not applicable

I just found the soultion:

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

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

Solution Sage

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

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!

Solution Sage

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

Solution Sage

@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:

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors