Forum Discussion
Previous Quarter value
Hi All,
This is a follow-up from my previous thread here
https://community.powerbi.com/t5/Power-Query/Calculate-the-previous-date-for-each-item/m-p/1043531
Since I created a new previous quarter column for the dates, I now want to create a measure to calculate the average previous score of it's previous quarter (see red text column for example)
| 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 | 6 |
| 5/2/2019 | Team A | 6 | 2019 | Q2 | Q1 | 2019 | 6 |
| 5/10/2019 | Team B | 6 | 2019 | Q2 | Q1 | 2019 | 7 |
| 5/10/2019 | Team B | 8 | 2019 | Q2 | Q1 | 2019 | 7 |
| 8/4/2019 | Team A | 6 | 2019 | Q3 | Q2 | 2019 | 7.5 |
| 8/4/2019 | Team A | 9 | 2019 | Q3 | Q2 | 2019 | 7.5 |
| 8/15/2019 | Team B | 9 | 2019 | Q3 | Q2 | 2019 | 9 |
| 8/15/2019 | Team B | 9 | 2019 | Q3 | Q2 | 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 |
i tried using the formula
=calculate(average(table[score]). previousquarter(Table[Date]))
But all I get are blank values when i try to check it in the table visuals. Hopefully someone can help with the confusion
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
4 Replies
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 formulas
