Forum Discussion
Keep highest value over time
Hi!
I have a dataset with a daily score for users. A sample could look like this:
| User | Points | Date |
| Ville | 1,3 | 2019-02-04 |
| Jon | 3,4 | 2019-02-04 |
| Tommy | 3,1 | 2019-02-04 |
| Ville | 2,1 | 2019-02-05 |
| Jon | 1,3 | 2019-02-05 |
| Tommy | 1,5 | 2019-02-05 |
I have a measure that calculates the average of my points. If I put it on a time axel it works well with days, weeks, month and so on. I want to show this particular result over months.
I want to keep the highest average by month though, so that it can only rise and never drop.
Lest say that the average of Ville by monthly points is:
January - 1,3
February - 1,0
March - 2,4
April - 1,7
I want the graph to stay on 1,3 over february, then rise to 2,4 in march and stay on 2,4 over April.
I think I'll need to store my highest value as a variable, but that way above my level on DAX queries. Any ideas?
Hi!
I've solved this!
I created my measure that calculated the average of point/score. I then created a measure that calculated the max by month.
Score max per Month =MAXX(KEEPFILTERS(VALUES('Calendar'[Date].[Month]));CALCULATE([Score]))I've got a table called Calendar that I've created myself and it holds all my dates.My measure to calculate the score is simply named "Score".
3 Replies
Hey,
can you please check the data in the table you provided.
Each date is seems to be in February.
If you have no solution until tomorrow, I will provide you a DAX statement.
One final question, does you datamodel makes us of a dedicated calendar table.
Regards,
Tom
Hi!
I've solved this!
I created my measure that calculated the average of point/score. I then created a measure that calculated the max by month.
Score max per Month =MAXX(KEEPFILTERS(VALUES('Calendar'[Date].[Month]));CALCULATE([Score]))I've got a table called Calendar that I've created myself and it holds all my dates.My measure to calculate the score is simply named "Score".
