Three year Average

[Anonymous]

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

Please help me.

[Anonymous]

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]

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.