Forum Discussion
Find changes in trend
Hello all,
I have played with Power BI a little, but when it comes to DAX I am overwhelmed and struggle to figure things out. I have a set of data that I'm trying to figure out how to identify changes in trends.
For example, my set of data looks similar to this, but with several thousand students across a minimum of 1 month. I have "truncated" the data for this example below. You can see StudentA and StudentB test scores are all relatively similar, but with StudentC something happened where their trend shifted and degraded. I am looking to see how in PowerBI I can identify all students that have a significant shift in their Score trend say over the past month and possibly up to the past 2-3 months.
I've been attempting to look online for how to get this to work, but it seems more complex than I realized. Tried using chatgpt, but everything it recommends results in some sort of errors or circular dependency issues. I'm stumped and was hoping some smart folks may have some suggestions on how I can accomplish this.
Cheers!
| Student | Date | Score |
| StudentA | 5/1/2025 | 98 |
| StudentA | 5/2/2025 | 98 |
| StudentA | 5/3/2025 | 98 |
| StudentA | 5/4/2025 | 100 |
| StudentA | 5/5/2025 | 100 |
| StudentA | 5/6/2025 | 99 |
| StudentA | 5/7/2025 | 95 |
| StudentA | 5/8/2025 | 97 |
| StudentA | 5/9/2025 | 96 |
| StudentA | 5/10/2025 | 96 |
| StudentB | 5/1/2025 | 99 |
| StudentB | 5/2/2025 | 98 |
| StudentB | 5/3/2025 | 100 |
| StudentB | 5/4/2025 | 95 |
| StudentB | 5/5/2025 | 100 |
| StudentB | 5/6/2025 | 96 |
| StudentB | 5/7/2025 | 95 |
| StudentB | 5/8/2025 | 97 |
| StudentB | 5/9/2025 | 95 |
| StudentB | 5/10/2025 | 97 |
| StudentC | 5/1/2025 | 100 |
| StudentC | 5/2/2025 | 96 |
| StudentC | 5/3/2025 | 100 |
| StudentC | 5/4/2025 | 96 |
| StudentC | 5/5/2025 | 95 |
| StudentC | 5/6/2025 | 99 |
| StudentC | 5/7/2025 | 70 |
| StudentC | 5/8/2025 | 63 |
| StudentC | 5/9/2025 | 66 |
| StudentC | 5/10/2025 | 61 |
In statistics, a moving average is the most frequently used way to identify trends with smoothing out short-term fluctuations. With PBI, it's even more informative by visulizing it.
i_like_tacos - You don't really need to implement any complicated DAX for this. You could do so with the right choice of visualisation. Customising a Scatter Plot would help visualise changes to the norm for each student.
This is a great article that will show you how: https://www.sqlbi.com/articles/using-scatterplots-to-find-details-in-reports/
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
4 Replies
In statistics, a moving average is the most frequently used way to identify trends with smoothing out short-term fluctuations. With PBI, it's even more informative by visulizing it.
i_like_tacos - You don't really need to implement any complicated DAX for this. You could do so with the right choice of visualisation. Customising a Scatter Plot would help visualise changes to the norm for each student.
This is a great article that will show you how: https://www.sqlbi.com/articles/using-scatterplots-to-find-details-in-reports/
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Thank you both. I've been able to implement the moving average window into my project and have been successful getting it to work. I am playing with it to try to expand functionality across other KPIs I'm working with.
The scatterplot method proposed is an interesting take and I am also playing with that and seeing how that may be incorporated as well.
Many Thanks!
i_like_tacos - I think Scatter plots are often the best visualisation for spotting outliers.
