Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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).
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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).
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for this. A bit confused on the few points
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.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.