Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Additional Filter Seems to Nullify Relative Date Filter in Function?

I have a simple measure that calculates a monthly average amount by category using a variable as numerator and a variable as denominator.  It works perfectly when set as:

Monthly Avg Amount = 
VAR Numerator = CALCULATE(
    SUM('All Transactions'[Transaction Amount]),
    DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-2,YEAR)) RETURN
VAR Denominator = CALCULATE(
    DISTINCTCOUNT('Calendar'[Mmm'YY]),
    DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-2,YEAR)) RETURN
DIVIDE(Numerator,Denominator,0
)

However, I have one more filter criteria I'd like to add to the numerator.  I want to exclude data rows marked as Outliers with an "x".  So my formula becomes:

Monthly Avg Amount = 
VAR Numerator = CALCULATE(
    SUM('All Transactions'[Transaction Amount]),
    DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-2,YEAR),
    FILTER('All Transactions','All Transactions'[Outlier]<>"x")) RETURN
VAR Denominator = CALCULATE(
    DISTINCTCOUNT('Calendar'[Mmm'YY]),
    DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-2,YEAR)) RETURN
DIVIDE(Numerator,Denominator,0
)

Adding this line appears to restrict the entire daterange of the numerator calculation to the current month (the denominator remains unaffected).  Why would adding this additional filter undo the relative date filtering function?

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

    All function will break filter effect on table, please use allselected function to instead.
    In addition, you can take a look at following blog to know more about how to use 'all' function:

    Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

    Please understand this link is provided with no warranties or guarantees of content changes, and confers no rights.

    Regards,

    Xiaoxin Sheng