Forum Discussion

saddas's avatar
saddas
Frequent Visitor
4 years ago
Solved

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 fo...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

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

    )

    )

  • smpa01's avatar
    smpa01
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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's avatar
saddas
Frequent Visitor
4 years ago

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

    )

)