cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jnoonan22 Frequent Visitor
Frequent Visitor

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?

1 REPLY 1
Community Support Team
Community Support Team

Re: Additional Filter Seems to Nullify Relative Date Filter in Function?

Hi @jnoonan22 ,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |