cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Three year Average

Hi, I need help with calculating 3-year average for selected Quarter.

If a user selects 2019.Q2 reporting period, then I want average of 2017.Q2, 2018.Q2, and 2019.Q2

I have a DATE table and slicer values are based on this DATE table.

I want output as shown in the picture.

 

I've Sales Measure

for LYSales am using SAMEPERIODLASTYEAR

 

3YrAvg.PNG

Please help me.

2 REPLIES 2
Anonymous
Not applicable

I forgot to mention that I have Report level filter to show only data from 2019.Q2 as we are going live/production with 2019.Q2 eventhough we have data previous years for Averages.

So I wanted to include logic to ignore Report level.Page level filters to get LY, LLY Sales.

Anonymous
Not applicable

Here is the solution:

              

LLYSales = CALCULATE([Sales],
                                             FILTER(ALL(dates), AND (Dates[Year] = SELECTEDVALUE(Dates[Year],ERROR("Please select Reporting Period"))-2,
                                                                                      Dates[Quarter]=SELECTEDVALUE(Dates[Quarter],ERROR("Please select Reporting Period")))))
If I use SELECTEDVALUE directly then only am getting correct output instead if I pass Measures which stores selected value of Year and Quarter its not working.
I verified YearSlected, QuarterSelected measures by placing on to a Card and am seeing correct values as 2019, and 2.
 
First I started testing with this below query:
                LLYSalesTest = CALCULATE([Saless],ALL(dates),Dates[Year]=2017, Dates[Quarter]=2) -- Prev Prev Year (2019-2)
 
All these Dates (Year and Quarter) are from DATE table, not Fact table.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors