topic Re: Three year Average in DAX Commands and Tips

Three year Average

Anonymous — Thu, 26 Sep 2019 15:19:45 GMT

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.

Re: Three year Average

Anonymous — Thu, 26 Sep 2019 20:10:58 GMT

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.

Re: Three year Average

Anonymous — Sat, 28 Sep 2019 14:57:57 GMT

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.