Forum Discussion
Solved
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 qua...
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
Thanks 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
Yes. 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.