Forum Discussion
Calculate Sum Excluding Specific Dates
I have a data set that includes the following columns:
Table 1: Date, City, Value.
I would like to Sum the values for a specific city, while also excluding specific dates.
I tried the following formula, which would work if the rows I wanted to exclude were text, rather than dates.
Paris Profit =
CALCULATE(
SUM( 'Table 1'[Value] ),
'Table 1'[City] = “Paris”,
KEEPFILTERS(
'Table 1'[Date] IN { "19-Jan-21", " 02-Apr-21" } = FALSE
)
)
The error I get is shown in the attached image.
Here is a link to the data set: https://www.dropbox.com/sh/9g6uvptyzazfl0u/AAAsElP-PuQxtnJOHCa-BGLWa?dl=0
Appreciate help with this. Thanks.
saddas , try like
Paris Profit =
CALCULATE(
SUM( 'Table 1'[Value] ),
'Table 1'[City] = “Paris”,
filter('Table 1'
not ( 'Table 1'[Date] = date(2021,01,19) || 'Table 1'[Date] = date(2021,04,21) )
)
)
4 Replies
saddas , try like
Paris Profit =
CALCULATE(
SUM( 'Table 1'[Value] ),
'Table 1'[City] = “Paris”,
filter('Table 1'
not ( 'Table 1'[Date] = date(2021,01,19) || 'Table 1'[Date] = date(2021,04,21) )
)
)
@amitchandak: thanks, your solution works. There was just a missing comma before the NOT operator. I also got my formula to work. For others who may be having a similar issue, here are the two formulas:
Option 1:
Paris Profit =
CALCULATE(
SUM( 'TABLE 1'[Value] ),
'TABLE 1'[City] = "Paris",
KEEPFILTERS(
'TABLE 1'[Date] IN { DATE (2021,01,19), DATE (2021,04,21) } = FALSE
)
)
Option 2:
Paris Profit =
CALCULATE(
SUM( 'TABLE 1'[Value] ),
'TABLE 1'[City] = "Paris",
FILTER('TABLE 1',
NOT ('TABLE 1'[Date] = DATE (2021,01,19) 'TABLE 1'[Date] = DATE (2021,04,21) )
)
)
Hi,
Ideally, you should have a Calendar Table with a relationship from the Date column of Table 1 to the Date column of the Calendar Table. Create a slicer from the Date field of the Calendar Table and select the dates you want. Write a simple measure
=SUM('Table 1'[Value])
