cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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:

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

Another mistake is that the DATE function is

DATE(year,month,day)

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

Resolver I

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

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

Solution Sage

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 🙂

Resolver I

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.

Resolver I

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

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

Another mistake is that the DATE function is

DATE(year,month,day)

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

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

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors