Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
The following is a sample user dataset where each user is assigned a rating at random times during the year (Some users are missing ratings for some of the years).
I have a matrix visual with the average rating per user, per year. (See example below). So John Smith had 2 enteries for the year 2020 and the matrix has a single average value for that user, for each year that they have data for.
What I'm trying to achieve is the Diff coloum (last coloum in 2nd visual), which is the difference between the 'latest average yearly rating' and 'earliest average yearly rating'. In Johns case this will be 1.225-0.936 (his value in 2021-2019). So "the earliest year" and "the latest year" with data could be different for each user.
How can I calculate this coloum/ measure with DAX?
Any help is much appreciated.
Solved! Go to Solution.
@Anonymous to be able to achieve this, you would need a Calendar Table. Once you have that, create a relationship between Fact[Date] and Calendar[Calendar_Date] which will look like below
Then you can write the following meaure to achieve what you need
Measure =
VAR _mxYear =
CALCULATE (
CALCULATE (
MAX ( 'Calendar'[Calendar_Year] ),
ALLEXCEPT ( 'Fact', 'Fact'[ID] )
),
CROSSFILTER ( 'Fact'[Date], 'Calendar'[Calendar_Date], BOTH )
)
VAR _minYear =
CALCULATE (
CALCULATE (
MIN ( 'Calendar'[Calendar_Year] ),
ALLEXCEPT ( 'Fact', 'Fact'[ID] )
),
CROSSFILTER ( 'Fact'[Date], 'Calendar'[Calendar_Date], BOTH )
)
VAR _minAvg =
CALCULATE (
AVERAGE ( 'Fact'[Rating] ),
FILTER (
VALUES ( 'Calendar'[Calendar_Year] ),
'Calendar'[Calendar_Year] = _minYear
)
)
VAR _maxAvg =
CALCULATE (
AVERAGE ( 'Fact'[Rating] ),
FILTER (
VALUES ( 'Calendar'[Calendar_Year] ),
'Calendar'[Calendar_Year] = _mxYear
)
)
RETURN
_maxAvg - _minAvg
From
to
@Anonymous to be able to achieve this, you would need a Calendar Table. Once you have that, create a relationship between Fact[Date] and Calendar[Calendar_Date] which will look like below
Then you can write the following meaure to achieve what you need
Measure =
VAR _mxYear =
CALCULATE (
CALCULATE (
MAX ( 'Calendar'[Calendar_Year] ),
ALLEXCEPT ( 'Fact', 'Fact'[ID] )
),
CROSSFILTER ( 'Fact'[Date], 'Calendar'[Calendar_Date], BOTH )
)
VAR _minYear =
CALCULATE (
CALCULATE (
MIN ( 'Calendar'[Calendar_Year] ),
ALLEXCEPT ( 'Fact', 'Fact'[ID] )
),
CROSSFILTER ( 'Fact'[Date], 'Calendar'[Calendar_Date], BOTH )
)
VAR _minAvg =
CALCULATE (
AVERAGE ( 'Fact'[Rating] ),
FILTER (
VALUES ( 'Calendar'[Calendar_Year] ),
'Calendar'[Calendar_Year] = _minYear
)
)
VAR _maxAvg =
CALCULATE (
AVERAGE ( 'Fact'[Rating] ),
FILTER (
VALUES ( 'Calendar'[Calendar_Year] ),
'Calendar'[Calendar_Year] = _mxYear
)
)
RETURN
_maxAvg - _minAvg
From
to
@Anonymous Hi!
Could you paste as a comment the table on which to calculate the required measurement?
Thx,
B.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
72 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
31 | |
27 | |
24 | |
22 |