Remove some filters but not all filters in a measure | REMOVEFILTERS() and ALL() does not work

[roeggelchen]

Hey all,

I might have a simple question, but I couldn’t find any solution. I have a List of delivery items with date and quantity. The quantity is positive, when an item is produced and negative, if the item is send. I want to know how many items are on stock. My DeliveryTable is connected to a DateTable.

DeliveryTable:

Item	Date	Quantity
A	01.01.2024	2
B	02.01.2024	5
A	05.01.2024	-1
B	06.01.2024	-3

There are filters on the measure, generated by the matrix (e.g. Date and Item)

Result wanted in a Matrix:

DateTable[Day]	01.01.2024	02.01.2024	03.01.2024	04.01.2024	05.01.2024	06.01.2024
A	2	2	2	2	1	1
B		5	5	5	5	2

I have tried the following options to generate the measure:

Option1 REMOVEFILTERS():

Stock =

VAR __LatestDate = MAX(DateTable[Date])

RETURN

CALCULATE(
SUM(DeliveryTable[Quantity]),
REMOVEFILTERS(DateTable[Date]),
REMOVEFILTERS(DeliveryTable[Date]),
FILTER(DeliveryTable, DeliveryTable[Date] <= __LatestDate)

Problem: This does not remove the filters, it just gives me the sum for the selected Date-Range.

Option2 - ALL():

Stock =

VAR __LatestDate = MAX(DateTable[Date])
VAR __Item = SELECTEDVALUE(DeliveryTable[Item])

RETURN

CALCULATE(
SUM(DeliveryTable[Quantity]),
REMOVEFILTERS(DateTable[Date]),
FILTER(ALL(DeliveryTable), DeliveryTable[Date] <= __LatestDate)
FILTER(ALL(DeliveryTable), DeliveryTable[Item] = __Item )

Problem: This works fine as long as I have only one Item selected.

Does anyone know, how I can solve this?

Thanks so much and best regards

Simon

[roeggelchen]

Hey @talespin ,

thanks so much, that works perfectly for me! You really safed my day!

Wish you all the best

Simon

[talespin]

You're welcome.

[talespin · Accepted solution]

hi @roeggelchen ,

This should do it.

Stock =

//Getting the maximum date in current filter context(from the matrix visual)

VAR _selDate = MAX('CALENDAR'[Date])

//Removing any filter on date and providing my own filter which says sum quantity for dates <= _selDate 

RETURN CALCULATE( SUM(TestTable9[Quantity]), ALL('CALENDAR'[Date]), 'CALENDAR'[Date] <= _selDate)