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 

could you show the data example?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

az38 
Sure thing, I just created a sample file, how do I share it?

 

 

    • 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]) ))