Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ARCherry
Frequent Visitor

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.
1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

Give date like

date(2020,1,15)

Pragati11
Super User
Super User

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

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

MFelix
Super User
Super User

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks Miguel! That has worked.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.