Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

Please help me.

LLY
ThreeYear Average

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.