# SumIf in PowerPivot 2013

• ### Question

• Trying to do a simple sumif in PowerPivot and I keep getting an #error message.

Can anyone help?

Calculated Field 2:=CALCULATE(SUM(Sales[NetSales],FILTER(Sales[Year]= 2015)),FILTER(Sales[MonthNo]= "01"))

Thanks
Thursday, July 21, 2016 5:11 PM

• Hi DeniseDD,

Please try the expression below.

`Copy of PYTDTotal:=CALCULATE(SUM(Sales[NetSales]),Sales[YrMth]<= (YEAR(now())-1 &"-"&RIGHT("00"&month(now()),2))) `
Regards,

Charlie Liao
TechNet Community Support

• Marked as answer by Tuesday, July 26, 2016 1:17 AM
Monday, July 25, 2016 6:07 AM

### All replies

• Calculate Field 2:= CALCULATE(SUM(Sales[NetSales]), FILTER(Sales, Sales[Year]=2015 && Sales[Month]="01"))

No closing ) for SUM. Extra ) after first filter.

You can combined the two conditions into one filter. && for AND, || for OR. There also is and AND() and OR() verb with comma separated fragments returning true/false.

It is easy to forget that DAX doesn't autocomplete like Excel :-)

Friday, July 22, 2016 4:15 PM
• Thank you for the response, I changed my formula to the below, but I am still getting the #error message.

Could the issue be that the month field is a calculated column for another field where the format is 2015-01,

2015-02?

Calculate Field 2:=CALCULATE(SUM(Sales[NetSales]), FILTER(Sales, Sales[Year]=2015 && Sales[Mth]="01"))

What I am trying to accomplish is sum all pervious sales though the current month.

I was using this formula but it will not work after sept, since I will no longer need the leading zero

Copy of PYTDTotal:=CALCULATE(SUM(Sales[NetSales]),Sales[YrMth]<= (YEAR(now())-1 &"-"&"0"& month(now())))

Thanks again

Friday, July 22, 2016 6:11 PM
• Hi DeniseDD,

Please try the expression below.

`Copy of PYTDTotal:=CALCULATE(SUM(Sales[NetSales]),Sales[YrMth]<= (YEAR(now())-1 &"-"&RIGHT("00"&month(now()),2))) `
Regards,

Charlie Liao
TechNet Community Support

• Marked as answer by Tuesday, July 26, 2016 1:17 AM
Monday, July 25, 2016 6:07 AM
• Great!

That works, thanks

Monday, July 25, 2016 1:28 PM
• Great!

That works, thanks

Hi Denise,

Glad to hear that your issue got solved. Please mark it as answer which will help other to find the solution easily.

Regards,

Charlie Liao
TechNet Community Support

Tuesday, July 26, 2016 1:16 AM