Search code examples
excelfilterpowerpivotdaxrolling-sum

PowerPivot Filter Function

In PowerPivot Excel 2016 I write a formula to summarize year to date sales using filter function as below:

SalesYTD:=CALCULATE (
[Net Sales], 
FILTER (
    ALL ( Sales), 
    'sales'[Year] = MAX ( 'Sales'[Year]  )
        && 'Sales'[Date] <= MAX ( 'Sales'[Date] )
  )
)

And it's work perfectly, now in my data I have a field called "Channel" which I want to filter it in my pivot table but it won't works! Does anybody knows how should I fix this formula?!

Thanks in advance...

Solution

  • Try:

    SalesYTD:=CALCULATE (
[Net Sales], 
FILTER (
    ALLEXCEPT ( 'Sales', 'Sales'[Channel] ), 
    'sales'[Year] = MAX ( 'Sales'[Year]  )
        && 'Sales'[Date] <= MAX ( 'Sales'[Date] )
  )
)

    ALLEXCEPT removes all context filters in the table except filters that have been applied to the specified columns, in this case [Channel] column.

    Let me know if this helps.