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...
LLY
ThreeYear Average
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.