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

try the above - failing that, is this what you're looking for?

setis's avatar
setis
Post Partisan
6 years ago

Dear amitchandak and Anonymous ,

 

Thanks a lot for your answers. The solution proposed by amitchandak works perfectly in the example, but I must be doing something wrong in my real project, since I'm still getting blanks. 

 

This is my measure:

 

# Cases Forecast-NotCY = 
VAR Year_selected =
    SELECTEDVALUE ( 'Date'[Calendar Year] )
RETURN
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Date'[Date] );
            CALCULATE (
                [# Cases];
                ALL ( 'Date'[Calendar Year] );
                NOT ( 'Date'[Calendar Year] = Year_selected )
            )
        )
    )

My #Cases measure is just a DISTINTCOUNT for Cases ID. 

 

would you be able to see why this isn't working for me here?

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Are you trying to get Avg no cases per day ??

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      then try something like that

      Avg Sales NO Day = 
var _year = SELECTEDVALUE('Date'[Year])
return
CALCULATE (
    Sales[# Sales],ALL('Date'[Year]),not('Date'[Year] =_year)
)/CALCULATE (
    count('Date'[Date]),ALL('Date'[Year]),not('Date'[Year] =_year)
)
      • setis's avatar
        setis
        Post Partisan
        6 years ago

        Dear amitchandak 

         

        Thanks a lot for your effort. I am still getting blanks with this one. 

         

        I'll try to reproduce the issue with a dummy report and post it here.