Calculate Sum Excluding Specific Dates

[saddas]

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.

[amitchandak · Accepted solution]

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

)

)

[Ashish_Mathur]

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

[smpa01 · Accepted solution]

@saddas  change to this

Paris Profit =
CALCULATE (
    SUM ( 'Table 1'[Value] ),
    'Table 1'[City] = "Paris",
    KEEPFILTERS (
        'Table 1'[Date]
            IN { DATE ( 2021, 1, 19 ), DATE ( 2021, 4, 2 ) } = FALSE
    )
)

[amitchandak · Accepted solution]

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

)

)

[saddas]

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

    )

)