Forum Discussion
Previous Quarter value
This measure should do what you are looking for. It works on a table like the one you've shown (e.g., where Team, Qtr, and Year are all included in the context of the visual, so the SelectedValue parts work).
Avg Prev Qtr = var prevyear = SELECTEDVALUE(Scores[Previous Qtr Yr])var prevqtr = SELECTEDVALUE(Scores[Prev Qtr])return CALCULATE(AVERAGE(Scores[Score (1-10)]), all(Scores), VALUES(Scores[Team]), Scores[Year]=prevyear, Scores[Prefix]=prevqtr)If it works for you, please mark it as the solution. Kudos are great too. Please let me know if it doesn't or if any questions.Regards,Pat
Apologies, the calculate average scores are not from their previous quarter. Correct table sample is below
| Date | Team | Score (1-10) | Year | Prefix | Prev Qtr | Previous Qtr Yr | Average score of previous quarter |
| 5/2/2019 | Team A | 6 | 2019 | Q2 | Q1 | 2019 | |
| 5/2/2019 | Team A | 6 | 2019 | Q2 | Q1 | 2019 | |
| 5/10/2019 | Team B | 6 | 2019 | Q2 | Q1 | 2019 | |
| 5/10/2019 | Team B | 8 | 2019 | Q2 | Q1 | 2019 | |
| 8/4/2019 | Team A | 6 | 2019 | Q3 | Q2 | 2019 | 6 |
| 8/4/2019 | Team A | 9 | 2019 | Q3 | Q2 | 2019 | 6 |
| 8/15/2019 | Team B | 9 | 2019 | Q3 | Q2 | 2019 | 7 |
| 8/15/2019 | Team B | 9 | 2019 | Q3 | Q2 | 2019 | 7 |
| 11/30/2019 | Team A | 8 | 2019 | Q4 | Q3 | 2019 | 7.5 |
| 11/30/2019 | Team A | 8 | 2019 | Q4 | Q3 | 2019 | 7.5 |
| 10/15/2019 | Team B | 10 | 2019 | Q4 | Q3 | 2019 | 9 |
| 10/15/2019 | Team B | 8 | 2019 | Q4 | Q3 | 2019 | 9 |
| 1/3/2020 | Team A | 8 | 2020 | Q1 | Q4 | 2019 | 8 |
| 1/3/2020 | Team A | 8 | 2020 | Q1 | Q4 | 2019 | 8 |
| 1/10/2020 | Team B | 10 | 2020 | Q1 | Q4 | 2019 | 9 |
| 1/10/2020 | Team B | 8 | 2020 | Q1 | Q4 | 2019 | 9 |
This measure should do what you are looking for. It works on a table like the one you've shown (e.g., where Team, Qtr, and Year are all included in the context of the visual, so the SelectedValue parts work).
Avg Prev Qtr = var prevyear = SELECTEDVALUE(Scores[Previous Qtr Yr])var prevqtr = SELECTEDVALUE(Scores[Prev Qtr])return CALCULATE(AVERAGE(Scores[Score (1-10)]), all(Scores), VALUES(Scores[Team]), Scores[Year]=prevyear, Scores[Prefix]=prevqtr)If it works for you, please mark it as the solution. Kudos are great too. Please let me know if it doesn't or if any questions.Regards,PatThanks for this. A bit confused on the few points
Avg Prev Qtr = var prevyear = SELECTEDVALUE(Scores[Previous Qtr Yr])var prevqtr = SELECTEDVALUE(Scores[Prev Qtr])return CALCULATE(AVERAGE(Scores[Score (1-10)]), all(Scores), VALUES(Scores[Team]), Scores[Year]=prevyear, Scores[Prefix]=prevqtr)Are the Var calculation of a different measure or that's part of the formula? Is still only one formula. Sorry for the noob question. still relative new to dax formulasYes. The variables are all part of the Avg Prev Qtr measure. They are simply storing the current values (on that row of the visual) for your previously determined previous year and qtr values. The variables are then used to refilter the data after the All() removes all the filters on the Scores table. The Values() is used to return the filter on Team. So it removes all filters, then refilters to the rows for the same team and prev year and qtr.