ALLEXCEPT not ignoring filters

[hahl]

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 amount based on the selected [Week]. The measure functions fine for the most part, but there are certain projects that return a blank value. When digging into the actual data for these projects, they exist and do have a JTD amount that it should return. When they return a blank, I think it's because the project does not contain the SELECTEDVALUE(Table[Week]).

So, I started testing to check if it was the SELECTEDVALUE(Table[Week]) portion that was causing the blanks.

To test, I created a measure like this: CALCULATE(MAX(Table[Week]), ALLEXCEPT(Table, Table[Week]))
This works fine on it's own, but as soon as I add a filter for Table[Project] it returns a blank value. 

All data is coming from the same table.
[Week] and [Date] are datetime
[Project] is text
[Amount] is number

Any ideas what I'm doing wrong?

[az38]

@hahl 

try

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

[hahl]

I tried this out, but it still returns blanks.

[amitchandak]

@hahl What you are trying to achieve using these measures.

In case you are looking for this week , last week rolling week etc refer :https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[hahl]

Thanks, I'm not looking for relative date filtering. I need JTD amounts where the week is the end date, so filtering by the week only wouldn't get me what I need.

[az38]

@hahl 

what else filter do you need? filter by project is included in my statement

[hahl]

Sorry @az38 I'm not good at tagging who I'm talking to 🙂

You got it right, the project filter is the only filter I need besides it looking back at all the historical data before the selected week.
I've been using one project as a example, because I've manually pulled out the data I want and it is there. But when I add a slicer for project and select this example project the measure both you and I created returns a blank value. Even when I can see it's not the case from the actual data. 

[az38]

@hahl 

could you show the data example?

[hahl]

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

[az38]

@hahl 

any cloud service like https://uploadfiles.io/

[hahl]

@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

123450	Friday, January 3, 2020	Saturday, January 4, 2020	5
123450	Wednesday, April 1, 2020	Saturday, April 4, 2020	8
123450	Wednesday, December 4, 2019	Saturday, December 7, 2019	78
234500	Wednesday, February 5, 2020	Saturday, February 8, 2020	99
234500	Sunday, March 8, 2020	Saturday, March 14, 2020	10
234500	Thursday, January 2, 2020	Saturday, January 4, 2020	64
156700	Thursday, January 2, 2020	Saturday, January 4, 2020	87

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. 

[v-alq-msft]

Hi, @hahl 

Based on your description, you may create a calculated table with the following formula.

Test = DISTINCT('Table'[End Week])

Then you may create a measure as below.

Result = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Date]<=SELECTEDVALUE('Test'[End Week])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hahl]

@az38  & @v-alq-msft 

Thank you both! Once I created a seperate table to use as my selected dates and removed the relationship between them it works! 🙂

[az38]

@hahl 

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

[hahl]

@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 · Accepted solution]

@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 ))