Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Burubear
Helper I
Helper I

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)

DateTeamScore (1-10)YearPrefixPrev QtrPrevious Qtr YrAverage score of previous quarter
5/2/2019Team A62019Q2Q120196
5/2/2019Team A62019Q2Q120196
5/10/2019Team B62019Q2Q120197
5/10/2019Team B82019Q2Q120197
8/4/2019Team A62019Q3Q220197.5
8/4/2019Team A92019Q3Q220197.5
8/15/2019Team B92019Q3Q220199
8/15/2019Team B92019Q3Q220199
1/3/2020Team A82020Q1Q420198
1/3/2020Team A82020Q1Q420198
1/10/2020Team B102020Q1Q420199
1/10/2020Team B82020Q1Q420199

 

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

1 ACCEPTED 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).

 

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
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
Burubear
Helper I
Helper I

Apologies, the calculate average scores are not from their previous quarter. Correct table sample is below

 

DateTeamScore (1-10)YearPrefixPrev QtrPrevious Qtr YrAverage score of previous quarter
5/2/2019Team A62019Q2Q12019 
5/2/2019Team A62019Q2Q12019 
5/10/2019Team B62019Q2Q12019 
5/10/2019Team B82019Q2Q12019 
8/4/2019Team A62019Q3Q220196
8/4/2019Team A92019Q3Q220196
8/15/2019Team B92019Q3Q220197
8/15/2019Team B92019Q3Q220197
11/30/2019Team A82019Q4Q320197.5
11/30/2019Team A82019Q4Q320197.5
10/15/2019Team B102019Q4Q320199
10/15/2019Team B82019Q4Q320199
1/3/2020Team A82020Q1Q420198
1/3/2020Team A82020Q1Q420198
1/10/2020Team B102020Q1Q420199
1/10/2020Team B82020Q1Q420199

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
 




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors