March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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:
Thanks in advance for any suggestion how to solve this.
Kind regards
Solved! Go to Solution.
@Anonymous
Another mistake is that the DATE function is
DATE(year,month,day)
So correct it to: DATE(__CurrentYear,__CurrentMonth,1)
I just found the soultion:
Instead of
Complete measure is:
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.
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'?
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 🙂
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 =
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
Thanks a lot!
I am almost there....
Within the variable
My bad...
Is it MONTH and not MONTHS and it is __Date1 and not Date1.
@Anonymous
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.
I just found the soultion:
Instead of
Complete measure is:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |