Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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

Any help is much appreciated.

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Create a measure.

    Diff_M = var max_date =CALCULATE(MAX('Table'[Date ]),ALLEXCEPT('Table','Table'[ID]))

var min_date = CALCULATE(MIN('Table'[Date ]),ALLEXCEPT('Table','Table'[ID]))

var Max_value= MAXX(FILTER(ALL('Table'),'Table'[ID]=SELECTEDVALUE('Table'[ID])&&'Table'[Date ]=max_date),'Table'[Rating])

var Min_value=MINX(FILTER(ALL('Table'),'Table'[ID]=SELECTEDVALUE('Table'[ID])&&'Table'[Date ].[Date]=min_date),'Table'[Rating])

return

Max_value-Min_value

     

    Or can create a column.

    Diff = var max_date =CALCULATE(MAX('Table'[Date ].[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])))

var min_date = CALCULATE(MIN('Table'[Date ]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])))

var Max_value= MAXX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date ].[Date]=max_date),'Table'[Rating])

var Min_value=MINX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date ].[Date]=min_date),'Table'[Rating])

return

Max_value-Min_value

    Best Regards

    Community Support Team _ polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • collinq's avatar
    collinq
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

     

    To accomplish this, I think that easiest method would be for you to create a DAX formula that determines the earliest and latest year and then subtract those from each other.  If you need help with the exact formula, I would suggest that you make a feqeust in the DAX Commands forum for assistance on the syntax.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Create a measure.

    Diff_M = var max_date =CALCULATE(MAX('Table'[Date ]),ALLEXCEPT('Table','Table'[ID]))

var min_date = CALCULATE(MIN('Table'[Date ]),ALLEXCEPT('Table','Table'[ID]))

var Max_value= MAXX(FILTER(ALL('Table'),'Table'[ID]=SELECTEDVALUE('Table'[ID])&&'Table'[Date ]=max_date),'Table'[Rating])

var Min_value=MINX(FILTER(ALL('Table'),'Table'[ID]=SELECTEDVALUE('Table'[ID])&&'Table'[Date ].[Date]=min_date),'Table'[Rating])

return

Max_value-Min_value

     

    Or can create a column.

    Diff = var max_date =CALCULATE(MAX('Table'[Date ].[Date]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])))

var min_date = CALCULATE(MIN('Table'[Date ]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])))

var Max_value= MAXX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date ].[Date]=max_date),'Table'[Rating])

var Min_value=MINX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date ].[Date]=min_date),'Table'[Rating])

return

Max_value-Min_value

    Best Regards

    Community Support Team _ polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.