Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

ALLEXCEPT not ignoring filters

Ultimately I am building a measure that looks like this: CALCULATE(SUM(Table[Amount]), FILTER(ALLEXCEPT(Table, Table[Project]), Table[Date] <=SELECTEDVALUE(Table[Week]))) This calculates a JTD ...
Need Help
  • az38's avatar
    az38
    6 years ago

    @hahl

    Yes, exactly. wrong copy and paste 😞

    Measure = 
var _endDate = CALCULATE(MAX('CalendarTable'[Date]))
RETURN
CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Project]),'Table'[Date]<= _endDate ))

    Безымянный.png

az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous 

ok, I got it

first, create a calendar table

CalendarTable = CALENDAR(MIN('Table'[Date]), MAX('Table'[End Week]))

then use as CalendarTable[Date] field as date slicer and create a measure in your table

Measure = 
var _endDate = CALCULATE(MAX('CalendarTable'[Date]),ALLSELECTED('Table'[End Week]))
RETURN
CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Project]),'Table'[Date]<=SELECTEDVALUE(CalendarTable[Date]) ))
Anonymous's avatar
Anonymous
Not applicable
6 years ago

az38 

This still returns as blank. Is the measure you provided missing the variable? You created a variable but didn't use it.

Also, I want my selection to be the End Week, and not the Date because the data will be displayed on a graph showing JTD amounts by Week. 

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    @hahl

    Yes, exactly. wrong copy and paste 😞

    Measure = 
var _endDate = CALCULATE(MAX('CalendarTable'[Date]))
RETURN
CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Project]),'Table'[Date]<= _endDate ))

    Безымянный.png