cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.  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 #### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features. #### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator. #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings. Top Solution Authors
Top Kudoed Authors
Users online (7,567)