Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter on Calculate Sum

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.
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

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

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    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)
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Miguel! That has worked.

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    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