Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Calculate the difference between yearly averages, for each individual user

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

 

DAX_PowerBI_1.PNG

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.

DAX_PowerBI_2.PNG

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.

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@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

smpa01_0-1634673744306.png

 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 

smpa01_1-1634673826064.png

to

smpa01_2-1634673862375.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@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

smpa01_0-1634673744306.png

 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 

smpa01_1-1634673826064.png

to

smpa01_2-1634673862375.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

Thank you @smpa01!  

BeaBF
Solution Sage
Solution Sage

@Anonymous Hi! 

Could you paste as a comment the table on which to calculate the required measurement?

 

Thx,

B.

Helpful resources

Announcements
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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