# 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

### 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,

`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, 