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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

az38 

Apologies, I wasn't able to get the upload to work. Possibly due to working from home.

Here's the sample table I created:

Project Date End Week Amount

123450Friday, January 3, 2020Saturday, January 4, 20205
123450Wednesday, April 1, 2020Saturday, April 4, 20208
123450Wednesday, December 4, 2019Saturday, December 7, 201978
234500Wednesday, February 5, 2020Saturday, February 8, 202099
234500Sunday, March 8, 2020Saturday, March 14, 202010
234500Thursday, January 2, 2020Saturday, January 4, 202064
156700Thursday, January 2, 2020Saturday, January 4, 202087



The End Week column is just the ending date of the week which corresponds to the Date (transaction date).
I was able to replicate the issue pretty easily with this data.

When I add the measure,

CALCULATE(SUM('Table'[Amount]), ALLEXCEPT('Table', 'Table'[Project]), FILTER(ALL('Table'), 'Table'[Date]<=SELECTEDVALUE('Table'[End Week])))

and then select an End Week in the slicer, it works perfectly.
I selected Feb 8th and the total was 333, which is the sum of all amounts before or equal to Feb 8th. 

To test the Project filter issue, I selected Project 123450. With Feb 8th still as the selected End Week, I want it to return a value of 83 (all data before the date of Feb 8th), but instead it returns blank.

Apologies again, hopefully this works for you. 


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