Forum Discussion

setis's avatar
setis
Post Partisan
6 years ago

Ignoring a filter

Dear experts, I am trying to create a measure to show me the sales amount per month for all years except 2009 (in my real project will be "current year" but for this excercise this can work)   My ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

have you tried using the calculate function like this;

sales = calculate(sum(sales), 
             year = 2019)

then using a month variable in your visual to break it out?
 
Thanks
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    the title doesn't really match the content - but if you want a visual to ignore a filter use 'Edit Interactions' under the format tab.

    • setis's avatar
      setis
      Post Partisan
      6 years ago

      Apologies for not explaining myself better in the title. 

       

      I can't do it in "Edit interactions" since I will have other columns related to the selected year.

       

      In this particular column I'll be using an AVERAGEX of the sales amount but I'll looking for to ignore the sales amount of the current year (or the 2009 year) as per the example attached. 

       

      The measure I'm working on on my real report is the following:

       

      # Cases Forecast- = 
VAR CasesNoCY =CALCULATE (
    [# Cases];
    FILTER (
        Cases ;
        NOT ( ( YEAR ( Cases[Date] ) ) = YEAR ( TODAY () ) )
    )
)
RETURN
CALCULATE(AVERAGEX(VALUES('Date'[Date]);CasesNoCY); ALL('Date'[Calendar Year]))

      The issue is that since in that table I have other columns using data for the current year, I can't filter it out for the visual. 

      The reason I need to eliminate the current year is to avoid using the actual Nr of Cases on the past dates for the current year forecast. 

      I hope that it makes sense.

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Try

        Sales NO CY = 
var _year = SELECTEDVALUE('Date'[Year])
return
CALCULATE (
    COUNTROWS ( Sales ),ALL('Date'[Year]),not('Date'[Year] =_year)
)