Filter on Calculate Sum

[ARCherry]

Hi All,

I am trying to do the below calculation to sum the Annual GP based a couple of filters, but as soon as I try and do the filter actualclosedate>X then it doesn't work and just sums all Annual GP. See below formula, can someone take a look and see where I am going wrong.

AGP Removed = CALCULATE(SUM(Opportunity[Annual GP]),Opportunity[new_ContractType]=279640002||Opportunity[new_ContractType]=279640000||Opportunity[new_ContractType]=957500000, Opportunity[ActualCloseDate]>15/01/2020)

I have tried the date format in UK and US but doesn't make a difference.

[amitchandak]

Give date like

date(2020,1,15)

[Pragati11]

Hi @,

 Can you please try the following DAX expression please:

AGP Removed = CALCULATE(
                                                SUM(Opportunity[Annual GP]),
                                                FILTER('Opportunity',
                                                             (Opportunity[new_ContractType]=279640002 || Opportunity[new_ContractType]=279640000                                                                 || Opportunity[new_ContractType]=957500000)

                                                               && (Opportunity[ActualCloseDate]>15/01/2020)    
                                                           )
                                              )

If this helpf give Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[MFelix · Accepted solution]

Hi @ARCherry ,

Try this measure:

AGP Removed =
CALCULATE (
    SUM ( Opportunity[Annual GP] ),
    (Opportunity[new_ContractType] = 279640002
        || Opportunity[new_ContractType] = 279640000
        || Opportunity[new_ContractType] = 957500000) &&
    Opportunity[ActualCloseDate] > DATE( 2020, 1, 15)
)

[ARCherry]

Thanks Miguel! That has worked.